Big Strings and Oracle Clobs

Oracle clobs are funny things. I've been trying to store a really long string as a clob into my Oracle database via JDBC and it's been a nightmare! In this post, I will describe all of the different approaches I tried and finish with the one which finally worked!

Setup:

String Size	7631 bytes
Database version	Oracle9i Enterprise Edition Release 9.2.0.8.0
Oracle NLS_CHARACTERSET	WE8ISO8859P1
JDBC driver version	Oracle Database 10g Release 2 (10.2.0.4)
Table Column DataType	NCLOB

Attempt 1: SetBigStringTryClob
In Oracle JDBC 10g, there is a new Connection property called SetBigStringTryClob which allows the statement's setString method to process strings greater than 32765 bytes.

Properties props = new Properties();
props.put("user", "username" );
props.put("password", "password");
props.put("SetBigStringTryClob", "true");

Connection conn = DriverManager.getConnection(dbUrl,props);
PreparedStatement st = conn.prepareStatement(INSERT_SQL);
st.setString(1, bigString);
st.executeUpdate();

This attempt failed - it inserted garbage (lots of inverted question marks and other funny characters) in my clob column.

Attempt 2: setStringForClob
The Oracle specific method of setStringForClob can be used for binding data greater than 32765 bytes. Note that this method is called internally if you call setString and have SetBigStringTryClob set to true (as in Attempt 1).

OraclePreparedStatement st = (OraclePreparedStatement)
          conn.prepareStatement(INSERT_SQL);
st.setStringForClob(1, bigString) ;

This attempt failed with the same result as previous one - it inserted garbage (lots of inverted question marks and other funny characters) in my Clob column.

Attempt 3: setCLOB
Create a temporary Oracle CLOB, populate it and call setClob.

CLOB clob = CLOB.createTemporary(conn,
  true,
  oracle.sql.CLOB.DURATION_SESSION,
  Const.NCHAR);
clob.trim(0);
Writer writer = clob.getCharacterOutputStream();
writer.write(bigString.toCharArray());
writer.flush();
writer.close();
st.setClob(1, clob);

This attempt failed with: ORA-12704: character set mismatch

Attempt 4: setCharacterStream
Use setCharacterStream to get a stream to write characters to the clob.

Reader reader = new StringReader(bigString);
int readerLength = bigString.toCharArray().length;
st.setCharacterStream(1, reader, readerLength);

Failed - garbage inserted again!

Attempt 5: Insert an empty_clob() and then update it
Insert an empty_clob() into the table, retrieve the locator, and then write the data to the clob.

String sql = "INSERT INTO clob_table (clob_col) "+
             "VALUES (empty_clob())";
PreparedStatement st = conn.prepareStatement(sql);
st.executeUpdate() ;

sql = "SELECT clob_col FROM clob_table FOR UPDATE";
st = conn.prepareStatement(sql);
ResultSet rs = st.executeQuery();
rs.next();
Clob clob = rs.getClob(1);
Writer writer = clob.setCharacterStream(0);
writer.write(bigString.toCharArray());
writer.flush();
writer.close();
rs.close();

Success! However, I'm not happy with the two database calls; one to create the empty clob and the other to update it. There must be a better way!

Attempt 6: PL/SQL
Wrap the SQL insert statement in PL/SQL to work around the size limitation.

INSERT_SQL = "BEGIN INSERT INTO clob_table (clob_col) "+
             "VALUES (?); END";
st = conn.prepareStatement(sql);
st.setString(1, bigString);
st.executeUpdate();

Success! And with only one database call!
Note, that setString can only process strings of less than 32766 chararacters, so if your String is bigger than this, you should use the empty_clob() technique from Attempt 5.

Phew! After six attempts, I've finally found two which work. Why does this have to be so complicated?!

References:
Oracle JDBC FAQ
Oracle 10g JDBC API
Using Oracle 10g drivers to solve the 4000 character limitation
Handling CLOBs - Made easy with Oracle JDBC 10g