Handling CLOBs – Made easy with Oracle JDBC 10g

Hi All,

I would like to share one of nice article on Handling CLOBs – Made easy with Oracle JDBC 10g.


What are CLOBs?

Basically, LOBs (Large Objects) are designed to support large unstructured data such as text, graphic images, still video clips, full motion video, and sound waveforms. A typical employee record may be a few hundred bytes, but even small amounts of multimedia data can be thousands of times larger. Oracle supports the following two types of LOBs:
* Those stored in the database either in-line in the table or in a separate segment or tablespace, such as BLOB(Binary LOB), CLOB (Character LOB) and, NCLOB (National Character LOB).
As the name signifies, BLOB holds binary data while the CLOB holds textual data and the NCLOB holds, character data that corresponds to the national character set defined for the Oracle database.
* Those stored as operating system files, such as BFILEs.

Managing CLOBs and BLOBs in JDBC is not very simple when compared to managing small amount of data. The way the CLOB and the BLOB objects are created and managed is different than a VARCHAR(String) column. In fact, Oracle extension classes are provided to support these types objects in JDBC like oracle.sql.CLOB, oracle.sql.BLOB etc.

The new enhancements in Oracle JDBC 10g promises to simplify the insertion and retrieval techniques for the CLOBs using the standard APIs. This document enumerates how easily the CLOB data can be handled in JDBC, by harnessing the new features of the Oracle JDBC 10g driver.

How To Handle CLOBs Easily in JDBC?

Prior to Oracle JDBC 10g, to manipulate the CLOB data in JDBC, Oracle extension class oracle.sql.CLOB was used. But now, Oracle JDBC 10g has a few enhancements that simplifies the CLOB manipulation in JDBC applications. This enables handling of large data using some of the available standard APIs, instead of using the Oracle extension classes. These could be thought as shortcuts for inserting and retrieving CLOB data from the database.

The enhanced standard APIs for manipulating CLOBs are the setString() and getString() of the PreparedStatement and ResultSet objects respectively. By default, the method preparedStatement.setString() will allow processing of the strings up to 32765 bytes. In order to insert data greater than 32765 bytes, a newly introduced Connection property – SetBigStringTryClob can be set. This forces the preparedStatement.setString() to use another newly introduced method, OraclePreparedStatement.setStringForClob() instead. This is actually done internally, transparent to the user.

Nevertheless, the newly introduced method OraclePreparedStatement.setStringForClob() alone can also be used instead of the standard APIs. This method makes the check on the data size internally again.

ResultSet.getString() can still be used to read the CLOB column. For the getString() and the setString()operations, the size limit for the string to be read or inserted is the one imposed by Java, that is, a positive int; the smallest being 0 or 1 byte.

Note: Do not use the setString() to bind large data to VARCHAR and LONG database columns, since it may truncate the data or cause errors.

In summary, PreparedStatement.setString() comes handy for processing the CLOB data, by just setting the Connection property SetBigStringTryClob. However, handling very large amounts of data this way may not be a wise; streaming the data is a better alternative.

Following is the code snippet to set the Connection property to process large data using the standard APIs. Refer the full source code in the file: ClobManipulationIn10g.java

import java.sql.Connection;
import java.sql.DriverManager;
import oracle.jdbc.OracleDriver;
import java.util.Properties;

// Load the database details into the variables.
String url      = “jdbc:oracle:thin:@localhost:1521:orcl”;
String user     = “scott”;
String password = “tiger”;

// Create the properties object that holds all database details
Properties props = new Properties();
props.put(“user”, user );
props.put(“password”, password);
props.put(“SetBigStringTryClob”, “true”);

// Load the Oracle JDBC driver class.
DriverManager.registerDriver(new OracleDriver());

// Get the database connection
Connection conn = DriverManager.getConnection( this.url, this.props );

The code snippet to create an Oracle database table with a CLOB column in it.

// SQL statement
CREATE TABLE clob_tab (clob_col CLOB);

Once the Connection property – SetBigStringTryClob is set, use the standard preparedStatement.setString() method for binding data more than 32765 bytes.

PreparedStatement pstmt = conn.prepareStatement(
“INSERT INTO clob_tab VALUES(?)”);

// Read a big file(larger than 32765 bytes).
// Note: method readFile() not listed here.
// It can be any method that reads a file.
String str = this.readFile(“bigFile.txt”);

// The string data is automatically transformed into a CLOB and
// inserted into the database column.
// Make sure that the Connection property – ‘SetBigStringTryClob’ is
// set to true for the insert to happen.
pstmt.setString(1, str);

Instead of the standard APIs, Oracle extension APIs can be used. OraclePreparedStatement.setStringForClob() can be used for binding data greater than 32765 bytes.

import java.sql.*;
import java.io.*;
import java.util.*;
import oracle.jdbc.*;
import oracle.jdbc.pool.*;

// Create SQL query to insert CLOB data and other columns in the database.
String sql = “INSERT INTO clob_tab VALUES(?)”;

// Read a big file(larger than 32765 bytes).
// Note: method readFile() not listed here.
// It can be any method that reads a file.
String str = this.readFile(“bigFile.txt”);

// Create the OraclePreparedStatement object
opstmt = (OraclePreparedStatement)conn.prepareStatement(sql);

// Use the new method to insert the CLOB data (for data greater or lesser than 32 KB)

// Execute the OraclePreparedStatement

Following is the code snippet that demonstrates the use of the standard ResultSet.getString() method, enhanced now to read more than 32765 bytes:

// Create a PreparedStatement object
PreparedStatement pstmt = null;

// Create a ResultSet to hold the records retrieved.
ResultSet rset = null;

// Create SQL query statement to retrieve records having CLOB data from
// the database.
String sqlCall = “SELECT clob_col FROM clob_tab”;
pstmt= conn.prepareStatement(sqlCall);

// Execute the PrepareStatement
rset = pstmt.executeQuery();

String clobVal = null;

// Get the CLOB value larger than 32765 bytes from the resultset
while (rset.next()) {
clobVal = rset.getString(1);
System.out.println(“CLOB length: “+clobVal.length());

Hope this help.

R Vashi

4 thoughts on “Handling CLOBs – Made easy with Oracle JDBC 10g

  1. props.put(“SetBigStringTryClob”, “true”);

    i tried to set the above property in my select statement while binding…
    but it is not working…

  2. Does oracle provide a legal way to update CLOB field (Oracle 10g)?
    I mean that using oracle classes instead of native jdbc-classes is NOT a “legal” solution.
    The applicaton must work with different databases transparently,
    and all oracle tricks should be hidden in a jdbc-driver.
    That’s the purpose of using “driver”, isnt it?
    I’ve got :
    java.lang.AbstractMethodError: oracle.jdbc.driver.T4CPreparedStatement.setClob(ILjava/io/Reader;)

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