Oracle Clob error on shared Sun One connection pool

[Jim Houd]

I have several web applications running on a Sun Java System App Server 7
2004 Q2 that need to access a table containing session information within a Clob data type on an Oracle 9i database.
Access is via a JNDI datasource.and connection pool,(using the oracle.jdbc.pool.OracleDataSource) setup on the app server.

After getting the functionality to create and retrieve Clobs in the database working in one of the applications
(once I figured out all that silly casting jdbc Clobs to Oracle Clobs)
I discovered that I get a java.lang.ClassCastException trying to cast the jdbc Clob to an Oracle Clob when the same function (adding Clobs on the same table) is performed by a second web application accessing the SAME jndi datasource & connection pool.

The code is:

PreparedStatement pstmt = null;
ResultSet rs = null;
String updateData =
"SELECT TX_DATA FROM WF_DATA WHERE ID_WF_DATA = ? FOR UPDATE ";
pstmt = con.prepareStatement(updateData);
pstmt.setLong(1, idWFData);
rs = pstmt.executeQuery();
if (rs.next() == false) {
throw new Exception("Error retrieving row");
}
oracle.sql.CLOB clob = (oracle.sql.CLOB) rs.getObject(1);

The last line above throws the ClassCastException exception when the connection pool is shared among web-apps.

I can work around it by creating a connection pool & jndi datasource for each of my web apps, but this isn't ideal since there will be many web apps, plus the overhead of each pool and datasource.

Anyone know how i can share the datasource/connection pool among web-apps and still use a single Clob table?