Inserting large XMLTYPE

Posted by Steve Racanovic | Posted in | Posted on 10:34 AM

0

When inserting large XMLTYPE, I am left with the following error:


java.sql.SQLException: ORA-01461: can bind a LONG value only for insert into a LONG column

at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:113)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:754)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:219)
at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:972)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1192)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3415)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3460)
at InsertXML.insertXMLType(InsertXML.java:47)
at InsertXML.main(InsertXML.java:93)

I found 2 ways to get around this problem.

1. XMLType are opaque types, so I should be using oracle.xdb.XMLType. So my code should look something like:

XMLType xml;
byte[] byteBuffer = xmlDetails.toString().getBytes();
InputStream is = new ByteArrayInputStream(byteBuffer);
xml = XMLType.createXML(connection,is);
pstmt.setObject(1,xml);


2. In Oracle JDBC 11.2.0.2. (Not implemented in 11.2.0.1) and using JDK 1.6 (utilising JDBC 4.0) we can use SQLXML Type - java.sql.SQLXML

http://download.oracle.com/docs/cd/E11882_01/java.112/e16548/jdbcvers.htm#BABGHBCC

So the code should look something like:

SQLXML x = conn.createSQLXML();
x.setString(xmlDetails.toString());
pstmt.setSQLXML(1, x);


The second option here is preferred and moving forward.