Writing to Clob-based Oracle 10g XMLTYPE columns

[nappinc]

Does anyone know if it is possible to write to a Clob-based XMLTYPE column using standard JDBC 3.0 APIs? We'd like to avoid using Oracle-specific APIs (and the Spring LobHandler work-around) so that monitoring software (such as JAMon which wraps connections in a java.lang.reflect.Proxy) can be used.

The Oracle 10g JDBC driver officially supports JDBC 3.0 "completely", but this has been my progress so far:

1. PreparedStatement.setString - throws ORA-01461 if xml > 4KB

2. PreparedStatement.setAsciiStream - throws ORA-01461 if xml > 4KB

3. Using "SetBigStringTryClob" connection property and PreparedStatement.setString - works if writing to a CLOB, but if writing to an XMLTYPE then throws ORA-01461 if xml > 4KB

e.g.

INSERT INTO MY_TABLE(ID,XML) VALUES (?,XMLTYPE(?))

4. Using "SetBigStringTryClob" and PreparedStatement.setObject, with type set to java.sql.Types.CLOB or java.sql.Types.VARCHAR - throws "java.lang.String" expected or ORA-00932.

5. Using oracle.sql.CLOB.createTemporary and PreparedStatement.setObject works fine but uses Oracle-specific APIs that require direct access to the underlying OracleConnection!

[nappinc]

Having raised this with Oracle support, I can clarify that the answer is "no".

If anyone is interested, the datails are:

Standard JDBC 3.0 APIs can be used in the 10g JDBC driver to read/write CLOBs (so Spring's LobHandler work-around is not needed), however these calls will fail if writing a CLOB that is then converted on the RDBMS server side to an XMLTYPE.

Looking at where the faults originate, it seems that what looks like being executed on the RDBMS server side is possibly being handled by the JDBC driver itself.

Anyway, hopefully JDBC 4.0 support will solve everything...

[eguy66]

...if writing to an XMLTYPE then throws ORA-01461 if xml > 4KB

Minor point, but in testing, I found that the exception is thrown at > 4,000 characters, not 4KB, i.e. 4.096 characters.

[nappinc]

Minor point, but in testing, I found that the exception is thrown at > 4,000 characters, not 4KB, i.e. 4.096 characters.

Obviously when I said 4KB I actually meant 4,000

4,000 is an internal limit inside Oracle for VARCHAR2 fields, so that would make sense.