Verifying approach to INSERT/UPDATE/SELECT of CLOB columns in Oracle 8/9 using Oracle Thin (JDBC Type IV) driver

From: Wes Gamble <w.gamble_at_pentasafe.com>
Date: 11 Dec 2002 15:20:24 -0800
Message-ID: <37fe2b65.0212111520.1d8dffe_at_posting.google.com>


All,

Thanks in advance for any help.

I have done quite a bit of reading on the topic of large object manipulation via JDBC in Oracle and wanted to verify that I am understanding correctly.

As have many people before me, I ran into the "well-known" 4KB limit on CLOB data in a standard, static INSERT or UPDATE statement. In order to allow for > 4 KB of data to be inserted/updated via my Java application, I am under the impression that I will have to do the following:

  1. Use a PreparedStatement for the operation
  2. Use PreparedStatement.setCharacterStream and ResultSet.getCharacterStream in order to write/read this data

Is this correct?

My question centers around the necessity of using the custom Oracle JDBC implementation classes provided with its driver in order to read/write the CLOB data. I reference the following two quotes:

"Oracle's implementation of PreparedStatement does not fully support
the manipulation of large objects like BLOBs and CLOBs. Specifically, the Thin driver does not support the use of the PreparedStatement object's setObject() and setBinaryStream() methods to set a BLOB's value, nor does it support the use of setCharacterStream() to set a CLOB's value. In addition, only methods in the locator itself, represented by a java.sql.Blob or a java.sql.Clob, can retrieve a LOB's value from the database. The fact that you can use a PreparedStatement to insert or update a LOB, but need to use a locator to retrieve a LOB's value, is inconsistent. Because of these two issues, I recommend you consistently use the locator's methods to insert, update, and retrieve LOB data." (from
http://www.onjava.com/lpt/a/1370)

"Important: The JDBC 2.0 specification states that
PreparedStatement methods setBinaryStream() and setObject() can be used to input a stream value as a BLOB, and that the PreparedStatement methods setAsciiStream(), setUnicodeStream(), setCharacterStream(), and setObject() can be used to input a stream value as a CLOB. This bypasses the LOB locator, going directly to the LOB data itself. In the implementation of the Oracle JDBC drivers, this functionality is supported only for a configuration using an 8.1.6 and higher database and 8.1.6 and higher JDBC OCI driver. Do not use this functionality for any other configuration, as data corruption may result." (from the Oracle9i JDBC Developer's Guide and Reference Version 2)

It seems as though I will bind my application to supporting only Oracle 8.1.6 or higher database servers if I use the standard JDBC interface. Is this the correct understanding? I know it seems explicit above, I just want some reassurance if you're willing to give it.

Thanks,
Wes Gamble Received on Thu Dec 12 2002 - 00:20:24 CET

Original text of this message