Steve's Blog Technical Information on Oracle Middleware.

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.