Oracle 8i & JDBC & CLOB

Oracle 8i & JDBC & CLOB

Post by Brian J. Sayatov » Fri, 04 Jul 2003 04:10:24



I've come across the need to store CLOBs in our Ouracle 8i databse via
JDBC.  Just setting a String on the PreparedStatement, I found out,
does not work.  Instead, I have to use setClob and give it a
java.sql.Clob object.

So, I then went on the search of finding out how to do this.  It seems
if I ran Oracle 9i, I could use oracle.sql.CLOB.createTemporary()
passing it my connection.  However, with 8i, I can't find such a
method.

However, I did see a ctor for CLOB that takes an OracleConnection.
So, I worte my code like this...

  public Clob getClob(java.sql.Connection pConnection)
    throws SQLException
  {
    CLOB clob = new CLOB((oracle.jdbc.driver.OracleConnection)pConnection);
    // ...

The problem I've ran into is that, running under WebSphere, the
Connection object I receive from my DataSource is not an actual
OracleConnection, but a proxy from IBM
(com.ibm.ejs.cm.proxy.OracleConnectionProxy).

The other 8i soltuion I've seen is to first do an INSERT with
empty_clob() and then do a SELECT to get the CLOB locate.  This
doesn't just look ugly -- it also tremednously complicated this core
piece of my persistence framework (the SQLProcessor pattern).

Can someone offer any ideas on how to make this work, or alternatives?

Regards,
Brian.