14 Replies Latest reply on Oct 26, 2009 3:27 PM by Joe Weinstein-Oracle

    ClassCastException in method CLOB.createTemporary

    434616

      Hello,

      I am trying to insert a CLOB into an Oracle table with the following code:

      String data = ...;
      Connection conn = ...;

      PreparedStatement ps = conn.prepareStatement("INSERT INTO BULK_REQUEST (BR_ID,NAAM,FILENAAM,REQUEST) VALUES (?,?,?,?)");

      ps.setLong(1, brId);
      ps.setString(2, requestName);
      ps.setString(3, filename);

      CLOB clob = CLOB.createTemporary(conn, true, CLOB.DURATION_SESSION); // Throws ClassCastException
      clob.open(CLOB.MODE_READWRITE);

      Writer clobWriter = clob.getCharacterOutputStream();
      clobWriter.write(data);
      clobWriter.flush();
      clobWriter.close();
      clob.close();

      ps.setObject(4, clob);

      ps.executeUpdate();


      When I create the temporary CLOB object using CLOB.createTemporary, I get a ClassCastException. This is what the stack trace shows:

      java.lang.ClassCastException
      at oracle.jdbc.driver.OracleConnection.physicalConnectionWithin(OracleConnection.java:5141)
      at oracle.sql.CLOB.createTemporary(CLOB.java:1009)
      at oracle.sql.CLOB.createTemporary(CLOB.java:956)
      at com.nuon.ict.ari.bulkconnection.DocumentUpload.doPost(DocumentUpload.java:109)
      at javax.servlet.http.HttpServlet.service(HttpServlet.java:760)


      The code runs inside a servlet that runs in Tomcat. The database connection is retrieved via JNDI, using a data source:

      Contect context = new InitialContext();
      Connection conn = ((DataSource)context.lookup("java:comp/env/jdbc/ari")).getConnection();


      I've searched the forum and found that others had a similar error, but I didn't find a solution that works.

      Why does this error occur; does it have anything to do with getting the database connection via the JNDI lookup? Is the actual Connection object of a different type than what CLOB.createTemporary expects? What is the solution to the problem?

      I'm using Oracle version 9 with ojdbc14.jar, the thin driver.

        • 1. Re: ClassCastException in method CLOB.createTemporary
          434616
          I have found the answer myself, and it indeed is what I suspected:

          Despite the fact that method CLOB.createTemporary takes a java.sql.Connection object as a parameter, 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.

          That is a BUG in the method CLOB.createTemporary !! (If only an OracleConnection object is allowed, the argument should be of type OracleConnection, not of type java.sql.Connection).

          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);
          ...

          I hope this helps when you get into the same trouble!

          Is there a place on this website where I can file a bug in Oracle's driver?
          • 2. Re: ClassCastException in method CLOB.createTemporary
            434616
            I noticed that class org.apache.commons.dbcp.DelegatingConnection has a method called getInnermostDelegate, which returns the innermost wrapped connection, so you could also use the following code:

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

            if (conn instanceof org.apache.commons.dbcp.DelegatingConnection) {
            oracleConnection = ((org.apache.commons.dbcp.DelegatingConnection)conn).getInnermostDelegate();
            }

            CLOB clob = CLOB.createTemporary(oracleConnection, true, CLOB.DURATION_SESSION);
            clob.open(CLOB.MODE_READWRITE);
            ...
            • 3. Re: ClassCastException in method CLOB.createTemporary
              435997
              Hello Dick:

              Thanks for posting the answer to your own question. Unfortunately I'm still a little stuck and I thought you might know something we don't. The thing is, i need to get the actual Oracle connection or statement, because I'm trying to use an oracle-specific feature (registerIndexTableOutParameter()), but I cannot cast the underlying connection even after the getInnermostDelegate().

              This is what I'm doing:
              OracleConnection oc = (OracleConnection) ((DelegatingConnection) c).getInnermostDelegate();

              If i break on that line my debugger shows the previous statement to return:
              oracle.jdbc.driver.OracleConnection@185e90f

              but I still get this exception when I continue:
              java.lang.ClassCastException: oracle.jdbc.driver.OracleConnection


              I've also tried to get innermost and cast statement itself:
              (oracle.jdbc.driver.OracleCallableStatement)((DelegatingCallableStatement) stmt).getInnermostDelegate()

              and again, debugger indicate I have what i want:
              oracle.jdbc.driver.OracleCallableStatement@16e1782

              but still exception is thrown:
              java.lang.ClassCastException: oracle.jdbc.driver.OracleCallableStatement


              This is what I want to do with the OracleStatement (works fine on Weblogic):
              ostmt.registerIndexTableOutParameter(3,1000, Types.VARCHAR, 300);

              Here's my context setup:
              <Context path="/OfferCode" docBase="OfferCode"
              debug="5" reloadable="true" crossContext="true">

              <Resource name="jdbc/devdb1b" auth="Container"
              type="javax.sql.DataSource" driverClassName="oracle.jdbc.OracleDriver"
              url="jdbc:oracle:thin:@10.176.50.65:1521:SERV"
              username="asdfasdf" password="asdf" maxActive="20" maxIdle="10"
              maxWait="-1" accessToUnderlyingConnectionAllowed="true"/>
              <ResourceParams name="jdbc/devdb1b">
              <parameter>
              <name>accessToUnderlyingConnectionAllowed</name>
              <value>true</value>
              </parameter>
              </ResourceParams>
              </Context>

              Thanks in advance for lending us a hand.

              -Mauricio
              • 4. Re: ClassCastException in method CLOB.createTemporary
                392572
                Hi Dick,

                looks like we've got the same problem (although mine is with BLOB - ClassCastException in oracle.sql.BLOB.createTemporary It's exactly as you say - even though the method accepts the java.sql.Connection interface as an input parameter it seems to try to cast it to an Oracle specific connection at some point. Of course this then doesn't work if used with a datasource (ironically not even with the Orion datasource shipped with OracleIAS!) I'll have a look on other forums and post here if I manage to crack it.

                regards,
                Dara
                • 5. Re: ClassCastException in method CLOB.createTemporary
                  194569
                  sorry my english :) hope it help!!

                  ..to obtain a oracle connection object from jndi you should use an oracle data source. in this case, the method getConnection directly instantiate an oracle connection on which you can call oracle methods via cast.
                  i think is incorrect to try to unwrap a wrapper.

                  code example that works:


                  ...to obtain jndi oracle connection from code java
                  try {           
                       Context initContext = new InitialContext();
                       Context envContext = (Context) initContext.lookup("java:/comp/env");
                       oracle.jdbc.pool.OracleDataSource ds = (oracle.jdbc.pool.OracleDataSource) envContext.lookup("jdbc/Montagna");
                       conn = ds.getConnection();
                  }
                       catch (Exception ex) {
                            ex.printStackTrace();
                  }
                  .............

                  ...configure web.xml
                  <resource-ref>
                  <description>Oracle Datasource example</description>
                  <res-ref-name>jdbc/mydatasource</res-ref-name>
                  <res-type>oracle.jdbc.pool.OracleDataSource</res-type>
                  <res-auth>Container</res-auth>
                  </resource-ref>
                  ...configure del web.xml

                  ...configure context
                  <Context displayName="..." docBase="..." path="/..." workDir="work\Catalina\localhost\...">

                  <Resource name="jdbc/mydatasource" auth="Container" scope="Shareable" type="oracle.jdbc.pool.OracleDataSource"/>
                  <ResourceParams name="jdbc/mydatasource">

                  <parameter>
                  <name>factory</name>
                  <value>oracle.jdbc.pool.OracleDataSourceFactory</value>
                  </parameter>

                  <parameter>
                  <name>maxWait</name>
                  <value>5000</value>
                  </parameter>
                  <parameter>
                  <name>maxActive</name>
                  <value>20</value>
                  </parameter>
                  <parameter>
                  <name>password</name>
                  <value>dgm</value>
                  </parameter>
                  <parameter>
                  <name>url</name>
                  <value>jdbc:oracle:thin:@...:1521:...</value>
                  </parameter>
                  <parameter>
                  <name>driverClassName</name>
                  <value>oracle.jdbc.driver.OracleDriver</value>
                  </parameter>
                  <parameter>
                  <name>maxIdle</name>
                  <value>2</value>
                  </parameter>
                  <parameter>
                  <name>user</name>
                  <value>dgm</value>
                  </parameter>
                  </ResourceParams>
                  </Context>
                  ... configurazione del context

                  piero/antonio
                  • 6. Re: ClassCastException in method CLOB.createTemporary
                    451015
                    &#25105;&#39030;!
                    • 7. Re: ClassCastException in method CLOB.createTemporary
                      451015
                      It is a good solution.But please note the following :
                      <name>url</name>
                      <value>jdbc:oracle:thin:@...:1521:...</value>
                      </parameter>
                      shuld be
                      <name>url</name>
                      <value>jdbc:oracle:thin:yourusename/yourpassword@...:1521:...</value>
                      </parameter>
                      Otherwise when excute "conn = ds.getConnection();",error occurs.
                      • 8. Re: ClassCastException in method CLOB.createTemporary
                        110569
                        Just in case the last solution doesn't work for everyone, the first solution does work but be sure to remove the oracle jar from your web app lib if you have it also defined in Tomcat. If that is the case you will get the class cast exception - it's a class loader issue.
                        • 9. Re: ClassCastException in while dealing with BLOBs  JBOSS
                          460409
                          jesper3 thx for posting your solution (while to trying to use BLOB's) I got inspired and found my solution for JBOSS with your solution

                          My problem was the same and I am using JBOSS,
                          For those who are using JBOSS to connect to Oracle here is the solution to get a working OracleConnection

                          ///////////////////////Code Snippet
                          DataSource auroraDataSource = (DataSource) context.lookup(DATA_SOURCE_NAME);
                          Connection conn=auroraDataSource.getConnection();
                                    
                          //depending on the type of Connection
                          //(this is very platform dependant JBOSS,SunOne...) because of the
                          //OracleConnection when using BLOB's          

                          //get the wrapped JBOSS connection
                          if(conn instanceof org.jboss.resource.adapter.jdbc.WrappedConnection){
                          WrappedConnection wc=(WrappedConnection)conn;
                          //with getUnderlying connection method , cast it to Oracle Connection
                          Connection oracleConnection=wc.getUnderlyingConnection();
                          }

                          //Do your BLOB operation

                          //create an empty blob (this is must)
                          stmt = conn.prepareStatement("INSERT INTO INFRA.UTL_OUTER_SYS_RAW_FILE VALUES ('myFile', empty_blob())");
                          stmt.execute();               

                          //select the previously added blob for update (SELECT FOR UPDATE)
                          stmt = conn.prepareStatement("SELECT FILE_CONTENT FROM INFRA.UTL_OUTER_SYS_RAW_FILE WHERE FILE_ID = 'myFile' FOR UPDATE");               
                          rs = stmt.executeQuery();
                                         
                          //write to BLOB
                          if(rs.next()) {
                          OracleResultSet resultSet = (OracleResultSet) rs;
                          fout = resultSet.getBLOB("FILE_CONTENT").getBinaryOutputStream();
                          fout.write(content);                                        
                          }

                          //close connections...
                          //////////////////////////////End of Code Snippet
                          • 10. Re: ClassCastException in while dealing with BLOBs  JBOSS
                            460409
                            There is missings in the code forgive me about that,
                            we are using the connection that we obtained after .getUnderlyingConnection method.
                            • 11. Re: ClassCastException in while dealing with BLOBs  JBOSS
                              ziggy25
                              Hi,

                              This thread was very helpfull. I tried this solution and i managed to solve it as shown below
                              if (innerConn instanceof DelegatingConnection){
                                                      innerConn = ((DelegatingConnection)innerConn).getDelegate();
                                                      conn = ((DelegatingConnection)innerConn).getDelegate();
                                                 }
                              I realise this is an old thread but any advice on the following questions which are related to the above issue will be appreciated.

                              - If i use the DelegatingConnection to retrieve the underlying connection, does that mean that the connection that i retrieve will no longer be managed by the Tomcat conneciton pool?
                              - If i issue the close() command on the connection will it close the connection or remove it from the connection pool?

                              Thanks
                              • 12. Re: ClassCastException in while dealing with BLOBs  JBOSS
                                Joe Weinstein-Oracle
                                Those are wise questions. Once you gain direct access to the actual
                                connection, you can certainly circumvent any pooling function. If
                                you close the original wrapper you got, the pooling system will
                                think it can give out the real connection to someone else, with a
                                new wrapper, but you still have a direct reference to it, so you can
                                interfere with the other thread. If you close the actual connection,
                                the pool won't necessarily even know, and might give it out to the
                                next user, with a new wrapper, but dead-on-arrival.
                                The lesson is to access the actual connection only when absolutely
                                necessary, and then don't close it, and don't keep a reference to
                                it any longer than you need it for the immediate purpose.
                                HTH,
                                Joe Weinstein

                                PS: In fact this is such a serious potential breach of pool integrity that
                                the WebLogic pooling system will by default prevent any such exposed
                                connection from ever being re-used. There has to be an explicit pool
                                configuration setting chosen to allow that connection to be re-used,
                                with the admin accepting the risk, and declaring that the application(s)
                                making the exposing calls are written safely.

                                Edited by: joe.weinstein@oracle.com on Oct 23, 2009 9:15 AM
                                • 13. Re: ClassCastException in while dealing with BLOBs  JBOSS
                                  ziggy25
                                  Hi, with the Tomcat connection pool, if i issue the close() command on the connection the connection does not close but it is put back in the queue.

                                  Just so that i understood you correctly, if i retrieve a conneciton from the pool, i should then retrieve the underlying connection from it. Once i have finished with the underlying connection i shouldnt close it but i should still close the original connection i retrieved from the Tomcat pool? Something like this
                                  Context initCtx = new InitialContext();
                                  Context envCtx = (Context) initCtx.lookup("java:comp/env");
                                  DataSource ds = (DataSource)
                                    envCtx.lookup("jdbc/EmployeeDB");
                                  
                                  Connection conn = ds.getConnection();
                                  Connection underlyingConn = null;
                                  
                                  if (conn instanceof DelegatingConnection){
                                                          underlyingConn = ((DelegatingConnection)underlyingConn).getDelegate();
                                                          underlyingConn = ((DelegatingConnection)underlyingConn ).getDelegate();
                                                     }
                                  
                                  
                                  //Do the oracle stuff here
                                  
                                  conn.close();
                                  underlyingConn = null;
                                  Will the above be safe then as i am not closing the underlying connection and i am only closing the original connection which should put the connection back in to the pool instead of physically closing it. I am setting the reference to the underlying connection reference to null. is this correct?

                                  Edited by: ziggy on Oct 26, 2009 7:43 AM

                                  Edited by: ziggy on Oct 26, 2009 8:36 AM