Using CLOBs with Oracle

Oracle provides a powerful tool for storing textual data within a database, namely Character Large OBjects (CLOBs.) The utilization of these objects is not entirely simple, however. Trying to use an SQL statement to populate CLOBs limits you to 4,000 bytes, rather than the 4GB stated capacity. Similarly, attempts to populate CLOBs using JDBC by specifying a character stream for an INSERT operation provides less than stellar results. Finally, there is no way to programmatically create a new CLOB and insert it directly into the database.

So how do you use CLOBs from JDBC? The answer is quite convoluted and even involves the manner in which a database table is defined. The overview is that you create a table row without initially populating the CLOBs. The table must be defined to use a default empty CLOB for the CLOB fields. You can then retrieve the row for update and use the CLOB references to populate the CLOBs. A complete programming example is included later in this document. For now, letís look at the specification of a sample table:

create table philstest (
partnerid varchar(3) not null,
transactionid integer not null,
request clob default EMPTY_CLOB(),
response clob default EMPTY_CLOB(),
unique( partnerid, transactionid ) );

The salient point here is that the request and response fields have been defined to use a default value of EMPTY_CLOB() (syntax is very important here.) Also note that we have created uniqueness on the table with a combination of the partnerid and transactionid fields. This is also important since we have to perform a two-step process and need to be able to uniquely access the record we create in order to perform the update.

It should be noted that I have used the oracle.sql classes rather than the java.sql classes. This makes the solution dependant on the back-end database but the solution should be portable to other databases without extensive revision (search and replace, mostly.) The majority of the steps should be quite familiar to anyone who has done any JDBC programming in the past so letís just skip to the stage where we create a new entry in the table:

conn = (OracleConnection) DriverManager.getConnection(
  dbConn, username, password );
stmt = (OraclePreparedStatement)
  conn.prepareStatement( "INSERT INTO PHILSTEST " +
  "( PARTNERID, TRANSACTIONID ) VALUES ( ?, ? )" );
stmt.setString( 1, partnerID );
stmt.setInt( 2, transactionID );
stmt.executeUpdate();
stmt.close();

Like I said, pretty standard stuff save for the casting of the returned objects to the Oracle-specific versions. At this point we have a row in the table which we can uniquely reference. So letís select that unique row:

conn.setAutoCommit( false );
stmt = (OraclePreparedStatement)
  conn.prepareStatement( "SELECT REQUEST, RESPONSE " +
  "FROM PHILSTEST WHERE PARTNERID = ? AND " +
  "TRANSACTIONID = ? FOR UPDATE" );
stmt.setString( 1, partnerID );
stmt.setInt( 2, transactionID );
rs = (OracleResultSet) stmt.executeQuery();
rs.next();

Note that we turn off auto-commit before beginning this operation. It turns out that this is very important to permit us to update the CLOBs and then manually commit the transaction upon completion. It retains a row lock on the database as indicated by the FOR UPDATE clause. I donít do any explicit error checking on the results from the call to the next method since, if the insert or select statements failed then they would have already thrown an SQLException (weíre doing this all in a try block, right?) Getting the CLOB references is simple:

CLOB request = rs.getCLOB( 1 );
CLOB response = rs.getCLOB( 2 );

This is one of the reasons I used the Oracle variants of the JDBC classes. There is no method called getCLOB in the java.sql classes, although there is a getClob. But I need the CLOB object since it has a method called getCharacterOutputStream which is a subclass of java.io.Writer which will permit me to easily populate the CLOB. Assuming that fileName is the name of a file derived elsewhere, here is the code which reads from the file and writes to the CLOB:

PrintWriter pw = new PrintWriter( response.getCharacterOutputStream() );
BufferedReader br = new BufferedReader( new FileReader( fileName ) );
String lineIn = null;
while( ( lineIn = br.readLine() ) != null )
	pw.println( lineIn );
br.close();
pw.close();

Now that was simple enough, wasnít it? Once weíve finished populating the CLOBs then itís simply a matter of commiting the changes and resetting the auto-commit:

stmt.close();
conn.commit();
conn.setAutoCommit( true );
conn.close();

Again, standard JDBC programming mechanisms. As you can see, it would be a trivial exercise to write the code which reads from the CLOB (hint: the method is called getCharacterStream and it returns a java.io.Reader.) Here's the complete code for a working solution for inserting records in the sample table.

import	oracle.sql.*;
import	oracle.jdbc.driver.*;
import	java.io.*;
import	java.sql.*;

public class CLOBtest {

	private static final String	dbConn =
	  "jdbc:oracle:thin:@localhost:1521:PTEK";
	private static final String	username = "joe";
	private static final String	password = "blow";

	public static void main( String args[] ) {
		CharArrayWriter		buff = null;
		BufferedReader		br = null;
		PrintWriter		pw = null;
		OracleConnection	conn = null;
		OraclePreparedStatement	stmt = null;
		OracleResultSet		rs = null;
		String		partnerID = null;
		int		transactionID = -1;
		String		driverName;
		String		lineIn = null;
		CLOB		request = null;
		CLOB		response = null;

		/*
		 * load the Oracle driver
		 */

		driverName = "oracle.jdbc.driver.OracleDriver";
		try {
			Class.forName( driverName );
		}
		catch( ClassNotFoundException e ) {
			e.printStackTrace();
			System.exit( 12 );
		}

		/*
		 * extract the command line arguments
		 */

		if( args.length != 4 ) {
			System.err.println( "Usage: CLOBtest partnerID " +
			  "transactionID requestFileName responseFileName" );
			System.exit( 12 );
		}
		partnerID = args[0];
		if( ( partnerID.length() < 1 ) || ( partnerID.length() > 3 ) ) {
			System.err.println( "CLOBtest: partnerID must contain between 1 and 3 characters" );
			System.exit( 12 );
		}
		try {
			transactionID = Integer.parseInt( args[1] );
		}
		catch( NumberFormatException e ) {
			System.err.println( "CLOBtest: transactionID must be numeric" );
			System.exit( 12 );
		}

		/*
		 * trap any SQL or other exceptions
		 */

		try {

			/*
			 * get the connection and create the statement
			 */

			conn = (OracleConnection) DriverManager.getConnection(
			  dbConn, username, password );
			stmt = (OraclePreparedStatement)
			  conn.prepareStatement( "INSERT INTO PHILSTEST " +
			  "( PARTNERID, TRANSACTIONID ) VALUES ( ?, ? )" );

			/*
			 * set the values and execute the insert
			 * NOTE: an SQLException will be thrown here if the
			 * partnerID/transactionID combination is not unique
			 */

			stmt.setString( 1, partnerID );
			stmt.setInt( 2, transactionID );
			stmt.executeUpdate();
			stmt.close();

			/*
			 * turn off auto-commit since we're going
			 * to lock a row for update
			 */

			conn.setAutoCommit( false );

			/*
			 * create the SELECT statement and execute
			 */

			stmt = (OraclePreparedStatement)
			  conn.prepareStatement( "SELECT REQUEST, RESPONSE " +
			  "FROM PHILSTEST WHERE PARTNERID = ? AND " +
			  "TRANSACTIONID = ? FOR UPDATE" );
			stmt.setString( 1, partnerID );
			stmt.setInt( 2, transactionID );
			rs = (OracleResultSet) stmt.executeQuery();

			/*
			 * got to the first (only) record in the result set
			 */

			rs.next();

			/*
			 * get the CLOB corresponding to the request
			 * and instantiate a PrintWriter using the
			 * character output stream for the CLOB
			 */

			request = rs.getCLOB( 1 );
			pw = new PrintWriter( request.getCharacterOutputStream() );

			/*
			 * read the input file and write to the CLOB
			 */

			br = new BufferedReader( new FileReader( args[2] ) );
			while( ( lineIn = br.readLine() ) != null )
				pw.println( lineIn );
			pw.close();
			br.close();

			/*
			 * do the same thing for the response
			 */

			response = rs.getCLOB( 2 );
			pw = new PrintWriter( response.getCharacterOutputStream() );
			br = new BufferedReader( new FileReader( args[3] ) );
			while( ( lineIn = br.readLine() ) != null )
				pw.println( lineIn );
			pw.close();
			br.close();

			/*
			 * close everthing down and commit
			 */

			stmt.close();
			conn.commit();
			conn.setAutoCommit( true );
			conn.close();
		}
		catch( Exception e ) {
			e.printStackTrace();
			System.exit( 12 );
		}
	}
}

Copyright © 2001, 2002, 2003, 2004, 2005, 2006, 2007, 2008, 2009, 2010, 2011, 2012, 2013, 2014, 2015 by Phil Selby
All rights reserved internationally.