Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.tools -> Problem inserting LONG field in Oracle8i db using Oracle's Java XML tools
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:@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 5000 characters, I get the following Exception:
C:\prj>java OracleXML putXML -conn
"jdbc:oracle:thin:@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.
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 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 this way limits the inserted data to a length of 4000 bytes (in Oracle 8i), 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 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 - 01:57:40 CDT