Verifying approach to INSERT/UPDATE/SELECT of CLOB columns in Oracle 8/9 using Oracle Thin (JDBC Type IV) driver
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:
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
"Important: The JDBC 2.0 specification states that
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
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)
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)
Thanks,
Wes Gamble
Received on Thu Dec 12 2002 - 00:20:24 CET