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

Yes I believe you are correct.

"Wes Gamble" <w.gamble_at_pentasafe.com> wrote in message news: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 Tue Dec 17 2002 - 23:57:13 CET