I'm not sure that this wouyld work, but are you able to use the various LOB
datatypes for this project?
--
Niall Litchfield
Oracle DBA
Audit Commission UK
Legal disclaimer required by my employer
****************************************************************************
**
This email contains information intended for the addressee only.
It may be confidential and may be the subject of legal and/or professional
privilege.
Any dissemination, distribution, copyright or use of this communication
without prior permission of the addressee is strictly prohibited.
****************************************************************************
**
"Joost van Dijk" <joostd_at_wanadoo.nl> wrote in message
news:3b4da197$0$11367_at_reader5...
> 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 Mon Jul 23 2001 - 07:06:10 CDT
