CREATE TABLE test(testid number, content clob);

String sql = "INSERT INTO test(testid, content) VALUES(?, EMPTY_CLOB())";
PreparedStatement pst = conn.prepareStatement(sql);
pst.setInt(1, maxid);
pst.executeUpdate();
sql = "SELECT content FROM test WHERE testid=? FOR UPDATE";
pst = conn.prepareStatement(sql);
pst.setInt(1, maxid);
ResultSet rs = pst.executeQuery();
if (rs.next()) {
    oracle.sql.CLOB clob = (oracle.sql.CLOB)rs.getClob(1);
    clob.putString(1, "Some content to write into database");
}

public static String clobToString(Object obj) {
    StringBuffer content= new StringBuffer("");
    if (obj != null) {
        CLOB clob = (CLOB)obj;
        Reader is;
        try {
            is = clob.getCharacterStream();
            BufferedReader br = new BufferedReader(is);
            String s = br.readLine();
            while (s != null) {
                content.append(s);
                s = br.readLine();
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }

    }
    return content.toString();
}

DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
String url = “jdbc:oracle:thin:@ubuntu:1521:oradb”;
String userName = “scott”;
String password = “tiger”;
conn = DriverManager.getConnection(url, userName, password);

<Resource name="jdbc/oraDB" auth="Container" type="javax.sql.DataSource"
   driverClassName="oracle.jdbc.driver.OracleDriver"
   factory="org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory"
   url="jdbc:oracle:thin:@server:1521:oradb"
   username="scott"
   password="tiger"
   maxWait="-1" removeAbandoned="true"
   maxActive="20" maxIdle="10"
   removeAbandonedTimeout="180" logAbandoned="true" accessToUnderlyingConnectionAllowed="true" />

public class DBConn {
    private static Context initCtx = null;
	  private static DataSource ds = null;
    private static String resource = "java:comp/env/jdbc/oraDB";

  	/**
  	 * Get a connection from pool
  	 *
  	 * @return a Connection
  	 */
  	public static Connection getConnection() {
  		  Connection conn = null;
  		  try {
  			    if (initCtx == null) {
  				      initCtx = new InitialContext();
  			    }
  			    if (ds == null) {
  				      ds = (DataSource) initCtx.lookup(resource);
  			    }
  			    try {
  				      conn = ds.getConnection();
  			    } catch (SQLException e1) {
  			    }
  		  } catch (NamingException e) {
  		  }
  		  return conn;
  	}
}

oracle.sql.CLOB clob = (oracle.sql.CLOB)rs.getClob(1);

It ONLY works if the Connection is an oracle.jdbc.driver.OracleConnection object. If it's not an oracle.jdbc.driver.OracleConnection, you get a ClassCastException.

The problem happens because when I run my code in Tomcat and get the database connection via a DataSource object that I lookup via JNDI, the connection is not a real OracleConnection object, but an object that wraps the real OracleConnection.

In more detail:

Tomcat uses the Apache Commons DBCP package (see http://jakarta.apache.org/commons/dbcp/) for database connection pooling. When you lookup a DataSource object using JNDI, like this:

Context context = new InitialContext();
DataSource ds = (DataSource)context.lookup("java:comp/env/jdbc/ari");

the object that you get is an instance of class org.apache.commons.dbcp.BasicDataSource. When you call getConnection on this object, you get an instance of class org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper (which obviously implements java.sql.Connection, and wraps the OracleConnection object).

To be able to call CLOB.createTemporary successfully, we need to find the wrapped OracleConnection object. A number of steps are necessary to reach this:

First of all, by default the PoolingDataSource$PoolGuardConnectionWrapper does not allow access to the wrapped Connection object (don't ask me why, but that's how the Apache developers decided it should be). You must enable access by configuring it in the <context> of the web application (in Tomcat's server.xml configuration file), for example:

<context path="/ariweb" docBase="ariweb.war">
<resource name="jdbc/ari" auth="Container" type="javax.sql.DataSource"/>
<resourceparams name="jdbc/ari">
<parameter>
<name>driverClassName</name>
<value>oracle.jdbc.driver.OracleDriver</value>
</parameter>
<parameter>
<!-- NOTE: This is necessary to enable access to the Oracle connection object -->
<name>accessToUnderlyingConnectionAllowed</name>
<value>true</value>
</parameter>
<!-- Other configuration parameters -->
...
</resourceparams>
</context>

The PoolingDataSource$PoolGuardConnectionWrapper extends class org.apache.commons.dbcp.DelegatingConnection, which has a getDelegate method. We can call this method to get the wrapped connection.

Unfortunately, the story is not yet finished. The getDelegate method of the PoolingDataSource$PoolGuardConnectionWrapper does not return the OracleConnection object - it returns an instance of class org.apache.commons.dbcp.PoolableConnection, which is a second wrapper around the OracleConnection object.

The PoolableConnection class also extends DelegatingConnection, so we can call getDelegate again and finally we have the OracleConnection object.

So here is the final code:

// conn is the Connection I got from the DataSource
Connection oracleConnection = conn;

if (conn instanceof org.apache.commons.dbcp.DelegatingConnection) {
// This returns a org.apache.commons.dbcp.PoolableConnection
Connection pc = ((org.apache.commons.dbcp.DelegatingConnection)conn).getDelegate();

// The PoolableConnection is a DelegatingConnection itself - get the delegate (the Oracle connection)
oracleConnection = ((org.apache.commons.dbcp.DelegatingConnection)pc).getDelegate();
}

CLOB clob = CLOB.createTemporary(oracleConnection, true, CLOB.DURATION_SESSION);
clob.open(CLOB.MODE_READWRITE);
...

conn = DBConn.getConnection();
if (conn instanceof DelegatingConnection) {
    // This returns a org.apache.commons.dbcp.PoolableConnection
    Connection pc = ((DelegatingConnection)conn).getDelegate();

    // The PoolableConnection is a DelegatingConnection itself - get the delegate (the Oracle connection)
    conn = ((DelegatingConnection)pc).getDelegate();
}

The two classes are the same (oracle.sql.CLOB), but they are loaded by different classloader. This usually happens when you put your JDBC driver jars both in server’s and webapp’s libraries and use the jndi datasource; the db objects (like CLOB) are created by the server (using its classloader) so when the webapp tries to cast those objects it gets that error, even if everything works fine when not using casts; you probably would have the same issue if you try to cast the ResultSet to OracleResultSet. I would recommend to put the JDBC driver(s) jars ONLY in the server lib dir; In Tomcat it would be the commons directory.

DELETE FROM A WHERE EXISTS(
SELECT (1) FROM A WHERE A.ColumnA=B.ColumnB AND …);

DELETE FROM EMP WHERE EXISTS(
SELECT (1) FROM EMP WHERE EMP.DEPTNO = DEPT.DEPTNO AND DEPT.DNAME = ‘Research’);

DELETE FROM EMP WHERE DEPTNO IN(
SELECT DEPTNO FROM DEPT WHERE DNAME = ‘Research’);

CREATE PROCEDURE batchimport
@db_name varchar(20)
AS
INSERT INTO employee SELECT * FROM @db_name..employee
GO

/*
Function: Merge many databases into a single one
Call: exec batchimport ‘database name’
*/

CREATE PROCEDURE batchimport
@db_name varchar(20)
AS

BEGIN
DECLARE @sql nvarchar(4000)

SELECT @sql = ‘INSERT INTO employee SELECT * FROM ‘ + quotename(@db_name) + ‘..employee’
EXEC sp_executesql @sql

END

GO

DELETE FROM table_name
WHERE rowid not in
(SELECT MIN(rowid)
FROM table_name
GROUP BY column1, column2, column3…));

SELECT * FROM v$reserved_words;