Jason’s Ranting & Raving

Those who don’t read have no advantage over those who can’t.

INSERT CLOB with JDBC and Oracle Prepared Statement

Posted by jaystile on May 24, 2010

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: