Problem inserting LONG field in Oracle8i db using Oracle's Java XML tools

[Quoted] I'm using Oracle's XDK in a project to upload data into an Oracle 8i database.
One of the tables that need to be inserted into contains a long field. This raises a problem for the OracleXML tool for inserts of size > 4000 bytes.
Does anyone know a way around this?

Some more details follow:

Here's the SQL create statement for my table:

CREATE TABLE mytable (

    id VARCHAR2 (10) NOT NULL,
    data LONG,
    CONSTRAINT PK_mytable PRIMARY KEY ( id ) );

Say I want to insert the data in the following XML document, stored in a file named long.xml:

<?xml version = '1.0'?>
<ROWSET>
  <ROW num="1">
<ID>1</ID>
<DATA>

      1234567890123456789012345678901234567890123456789
</DATA>

  </ROW>
</ROWSET>

I can do this by using the OracleXML class (which is a command line interface for OracleXMLSave - the class that I'm actually using) from the Oracle XDK:

C:\prj>java OracleXML putXML -conn
"jdbc:oracle:thin:_at_myhost:1521:myinst" -user "myusername/mypassword" -filename long.xml mytable successfully inserted 1 rows into mytable

However, if the data element in the XML document is longer, say a string of [Quoted] 5000 characters, I get the following Exception:

C:\prj>java OracleXML putXML -conn
"jdbc:oracle:thin:_at_myhost:1521:myinst" -user "myusername/mypassword" -filename long.xml mytable oracle.xml.sql.OracleXMLSQLException: 'java.sql.SQLException: Data size bigger than max size for this type: 5000' encountered during processing ROW element 0.
[Quoted] All prior XML row changes were rolled back. in the XML document.

        at oracle.xml.sql.dml.OracleXMLSave.saveXML(OracleXMLSave.java:2315)
        at oracle.xml.sql.dml.OracleXMLSave.saveXML(OracleXMLSave.java:2189)
        at
[Quoted] oracle.xml.sql.dml.OracleXMLSave.insertXML(OracleXMLSave.java:1278)
        at OracleXML.Put_XML(OracleXML.java:477)
        at OracleXML.ExecutePutXML(OracleXML.java:395)
        at OracleXML.main(OracleXML.java:179)

Apparently, the OracleXMLSave class tries to upload the data through a standard SQL INSERT statement. It is a known problem that inserting data in [Quoted] this way limits the inserted data to a length of 4000 bytes (in Oracle 8i), [Quoted] this being the maximum string literal size. As described in chapter 3 of the Oracle JDBC Developer's Guide and Reference, this problem can be solved by streaming LONG columns, i.e. using [Quoted] something like PreparedStatement.setAsciiStream() in Java.

Is there a way to force OracleXMLSave to use streaming in database inserts?

Cheers,

    Joost Received on Sun Jul 22 2001 - 08:57:40 CEST