INSERT CLOB with JDBC and Oracle Prepared Statement

The oracle API made me crazy for about six hours. That is how long it took me before I could figure out how to do an insert that includes a clob. The key to do an insert with a clob is to use the oracle.sql.CLOB.createTemporary method. The javadoc associated with the CLOB class is very sparse and does not include any instructions.

In my scenario I was trying to do an insert with a prepared statement. The Prepared Statement has a setClob method, but it really doesn’t do you any good because you don’t have a clob object available. Most of the examples I found showed a technique of creating an empty clob object and then getting a reference to the clob and then streaming data to it. This was not going to work for me because I’m running batches of statements with thousands of inserts.

This is what worked for me. It might need a little tweaking since I’m blogging from home without my development environment, so take it with a grain of salt.

// Pseudo-code
import oracle.sql.CLOB
import java.sql.Connection
import java.sql.PreparedStatement
...
public void setClobInStatement(Connection conn, PreparedStatement ps, String longText) {

// I don't know what 'false' means versus 'true' in the following statement
CLOB c = CLOB.createTemporary(conn, false, CLOB.DURATION_SESSION);

// The 1 is the position to start inserting the string into the clob.
c.setString(1, longText);

// Set the temporary clob in the statement, it becomes permanent when the statement executes.
// If the clob is the first object to be set in the statement, use 1
ps.setClob(1, c);
}


Gotcha’s

• The PreparedStatement.setString() will work with text up to 4000 characters. With strings longer than that it will fail with a cryptic exception, ORA-00600, I believe.
• If you’re supporting multiple databases, you’ll have to create an Oracle specific class and implement an interface so you can swap out the implementation. This requires direct use of the Oracle API.

References
La Javadoc En Francais (It’s in english and the most complete)
oracle.sql.CLOB
oracle.jdbc.driver.OraclePreparedStatement