Oracle, XMLType, Spring and a couple of wasted hours

I had written an application that reads a set of XML files and writes them to an Oracle database.
I was using Spring configuration injection to get a DataSource and Connection and Spring JDBCTemplate to read and write to the database.

The field in the database I should write to was an Oracle XMLType, which is a kind of clob.

At the beggining to write using the JDBCTemplate I would use the following:

LobHandler lobHandler = new DefaultLobHandler();
Object clob = new SqlLobValue(xmlString, lobHandler);
insert.update(new Object[] {new Long(key),...

That did the job until today, when we needed XML files bigger than 4k bytes to be written.
I started getting the following error:

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

It happens that the LONG for oracle is the CLOB, but it just treats as CLOB when the sizes is bigger than 4k (or somewhere around).

First I try to use OracleLobHandler instead of the Default one, then it would give:

org.springframework.dao.InvalidDataAccessApiUsageException: OracleLobCreator needs to work on [oracle.jdbc.OracleConnection], not on [org.apache.commons.dbcp.PoolableConnection]: specify a corresponding NativeJdbcExtractor; nested exception is java.lang.ClassCastException: org.apache.commons.dbcp.PoolableConnection
java.lang.ClassCastException: org.apache.commons.dbcp.PoolableConnection

If I use directly an OracleDataSource on my spring configuration or if I would apply this NativeJdbcExtractor I would then got:

ORA-00932: inconsistent datatypes: expected NUMBER got CLOB

Ok, now I'm totally stuck I thought. Then I completely gave up using JdbcTemplate and went straight to JDBC. Well, even then things didn't go as smooth as I thought they would.

First thing, after some research I had to use the following getClob method, which I got in this Oracle OTN page here:

private CLOB getCLOB(String xmlData, Connection conn) throws Exception{
CLOB tempClob = null;
tempClob = CLOB.createTemporary(conn, true, CLOB.DURATION_SESSION);
tempClob.open(CLOB.MODE_READWRITE);
Writer tempClobWriter = tempClob.getCharacterOutputStream();
tempClobWriter.write(xmlData);
tempClobWriter.flush();
tempClobWriter.close();
tempClob.close();

return tempClob;
}

Then when setting it inside your preparedstatement:

CLOB clob = getCLOB(myStringObj, connection);
stmt.setClob(4, clob);

Also, in your SQL you should have something like:

insert into table (XML) values(XMLType(?)

Ok, after all this I still got the following stacktrace:

java.lang.ClassCastException: org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper
at oracle.sql.CLOB.createTemporary(CLOB.java:754)

Then I tried to use instead of org.apache.commons.dbcp.BasicDataSource to use directly OracleDataSource, which worked, but the properties to set are different from those I used for DBCP, url is URL and username is user and I would like to keep it generic. That's when I tried to use Spring's org.springframework.jdbc.datasource.DriverManagerDataSource for my datasource configuration, which then finally got it working.

After this I tried to get back to have it working through JdbcTemplate and the new DataSource, but with no luck.