ClassCastException casting to OracleConnection from a Data Source Connection in OAS 10.1.3.x

I have previously blogged how to access a data source remotely using OAS 10.1.3.x as shown below.

http://theblasfrompas.blogspot.com/2007/07/access-data-source-from-oc4j-remotely.html

Normally I cast back to a java.sql.Connection but you can also cast to an OracleConnection, as shown below.

OracleConnection oraConnection = null;
InitialContext ic = new InitialContext(env);
DataSource ds = (DataSource) ic.lookup("jdbc/scottDS");
oraConnection = (OracleConnection) ds.getConnection();

When you do this you may get a runtime exception as follows:

Exception in thread "main" java.lang.ClassCastException: oracle_jdbc_driver_LogicalConnection_Proxy

The problem here is you must import the correct OracleConnection. The one you want to import is "oracle.jdbc.OracleConnection". You end up with the error above if you use "oracle.jdbc.driver.OracleConnection".

Cancelling Long Running Queries with 11g JDBC Thin Driver

With the introduction of the 11g JDBC driver calling Statement.cancel() will actually cancel a long running query where in 10g JDBC Driver this would not work. Now with the 11g JDBC driver the cancel request is sent out of band. The out-of-band implementation may allow the database server to process the request immediately and terminate the system call that would otherwise block the query. This database feature is OS dependent and may not work on all platforms. I tested on Linux x86-32bit system with a 11.1.0 Database and it worked fine.

Java Code:

CancelQuery11G.java

package pas.jdbc.cancel;

import java.io.BufferedReader;

import java.io.IOException;
import java.io.InputStreamReader;

import java.sql.Connection;

import java.sql.ResultSet;
import java.sql.SQLException;

import java.sql.Statement;

import oracle.jdbc.pool.OracleDataSource;

public class CancelQuery11G
{
private OracleDataSource ods = null;
public final String userId = "scott";
public final String password = "tiger";
public final String url =
  "jdbc:oracle:thin:@myhost:1521:linux11g";

public CancelQuery11G() throws SQLException
{
  ods = new OracleDataSource();
  ods.setUser(userId);
  ods.setPassword(password);
  ods.setURL(url);
}

public void run () throws SQLException, IOException
{
  Connection conn = null;
  Statement stmt = null;

  ResultSet rset = null;

  try
  {
    conn = getConnection();
    getDriverDetails(conn);
    stmt = conn.createStatement();

    CancelThread ct = new CancelThread(stmt);
    Thread th = new Thread(ct);
    th.start();
    Thread.sleep(1000);
    System.out.println("Hit enter to cancel:");
    BufferedReader stdin = new BufferedReader( new InputStreamReader( System.in ) );
    String input = stdin.readLine();

    System.out.println("Cancelling Query");
    stmt.cancel();
    System.out.println("Query Cancelled");

    System.out.println("Hit enter to finish:");
    stdin.readLine();
 
  }
  catch (SQLException e)
  {
    System.out.println("SQLException in run() : ");
    e.printStackTrace();
  }
  catch (IOException ioe)
  {
    ioe.printStackTrace();
  }
  catch (InterruptedException ie)
  {
    ie.printStackTrace();
  }
  finally
  {
    if (ods != null)
      ods.close();
   
    if (conn != null)
      conn.close();
  }

}

private Connection getConnection() throws SQLException
{
  return ods.getConnection();
}

private void getDriverDetails (Connection conn) throws SQLException
{
  java.sql.DatabaseMetaData meta = conn.getMetaData ();

  // gets driver info:

  System.out.println("=============\nDatabase Product Name is ... " +
  meta.getDatabaseProductName());
  System.out.println("Database Product Version is " +
  meta.getDatabaseProductVersion());
  System.out.println("=============JDBC Driver Name is ........ " +
  meta.getDriverName());
  System.out.println("JDBC Driver Version is ..... " +
  meta.getDriverVersion());
  System.out.println("JDBC Driver Major Version is " +
  meta.getDriverMajorVersion());
  System.out.println("JDBC Driver Minor Version is " + meta.getDriverMinorVersion());
  System.out.println("=============");
}
public static void main(String[] args) throws Exception
{
  CancelQuery11G test = new CancelQuery11G();
  test.run();
  System.out.println("Completed");
}
}

CancelThread.java

package pas.jdbc.cancel;

import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSet;

public class CancelThread implements Runnable
{
private Statement stmt = null;
String longRunningSQL =
  "select * from all_objects";
private int i = 0;

public CancelThread(Statement oracleStmt)
{
  stmt = oracleStmt; 
}

public void run()
{
  ResultSet rset = null;

  try
  {
    System.out.println("Executing statement....");
    rset = stmt.executeQuery(longRunningSQL);
    System.out.println("Processing resultset now..");
    while (rset.next())
    {
      i++;
    }
    System.out.println("Finished with resultset and statement now..");
  }
  catch (SQLException e)
  {
    e.printStackTrace();
  }
  finally
  {
    try
    {
      if (rset != null)
        rset.close();
   
      if (stmt != null)
        stmt.close();
    }
    catch (SQLException e)
    {
   
    }
  }

}
}

Output:

=============
Database Product Name is ... Oracle
Database Product Version is Oracle Database 11g Release 11.1.0.0.0 - Production
=============
JDBC Driver Name is ........ Oracle JDBC driver
JDBC Driver Version is ..... 11.1.0.6.0-Production
JDBC Driver Major Version is 11
JDBC Driver Minor Version is 1
=============
Executing statement....
Processing resultset now..
Hit enter to cancel:

Cancelling Query

java.sql.SQLException: ORA-01013: user requested cancel of current operation

at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:119)
at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:115)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:221)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:467)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:417)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1084)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:187)
at oracle.jdbc.driver.T4CStatement.fetch(T4CStatement.java:1312)
at oracle.jdbc.driver.OracleResultSetImpl.close_or_fetch_from_next(OracleResultSetImpl.java:606)
at oracle.jdbc.driver.OracleResultSetImpl.next(OracleResultSetImpl.java:493)
at pas.jdbc.cancel.CancelThread.run(CancelThread.java:28)
at java.lang.Thread.run(Thread.java:595)

Query Cancelled
Hit enter to finish:

Completed

Variable in list with Oracle JDBC and RDBMS

I always wanted to create a variable in list query using a single parameter and found that it wasn't possible with JDBC.

SELECT * FROM DEPT WHERE like DEPTNO IN (?);

Then I stumbled upon this on steve's blog, which he shows how he did it in ADF BC using a SQL function.

http://radio.weblogs.com/0118231/stories/2004/09/23/notYetDocumentedAdfSampleApplications.html
126. Using Comma-Separated String Bind for Variable IN List [10.1.3.3] 10-JAN-2008

So his my simple JDBC program method showing how it works, using the DEPT table here.

public void run () throws SQLException
{
  Connection conn = null;
  PreparedStatement stmt = null;
  ResultSet rset = null;
  String queryInList =
    "SELECT DEPTNO, " +
    "       DNAME, " +
    "       LOC " +
    "FROM DEPT " +
    "WHERE DEPTNO IN " +
    "(SELECT * FROM TABLE(CAST(in_number_list(?) as num_table)))";

  try
  {
    conn = getConnection();
    stmt = conn.prepareStatement(queryInList);
    stmt.setString(1, "10, 20, 30");
    rset = stmt.executeQuery();
 
    while (rset.next())
    {
      System.out.println("Dept [" + rset.getInt(1) + ", " +
                         rset.getString(2) + "]");
    }
  }
  catch (SQLException e)
  {
    System.out.println("SQLException occurred");
    e.printStackTrace();
  }
  finally
  {
    if (rset != null)
      rset.close();

    if (stmt != null)
      stmt.close();
   
    if (conn != null)
      conn.close();
  }
}

So the output is as follows showing that only records 10, 20, 30 will be returned.

Dept [10, ACCOUNTING]
Dept [20, RESEARCH]
Dept [30, SALES]

Note: SQL being used is as follows

CREATE TYPE num_table AS TABLE OF NUMBER;
/
CREATE OR REPLACE FUNCTION in_number_list (p_in_list  IN  VARCHAR2)
  RETURN num_table
AS
  l_tab   num_table := num_table();
  l_text  VARCHAR2(32767) := p_in_list || ',';
  l_idx   NUMBER;
BEGIN
  LOOP
    l_idx := INSTR(l_text, ',');
    EXIT WHEN NVL(l_idx, 0) = 0;
    l_tab.extend;
    l_tab(l_tab.last) := to_number(TRIM(SUBSTR(l_text, 1, l_idx - 1)));
    l_text := SUBSTR(l_text, l_idx + 1);
  END LOOP;

  RETURN l_tab;
END;
/
show errors

Incremental deployment of EJB JAR file fails in ASC

If you try to redeploy an EJB JAR file in ASC you will get a runtime error as follows.

[Dec 24, 2007 7:41:48 AM] Application Deployer for hotdeployEAR STARTS.
[Dec 24, 2007 7:41:48 AM] Stopping application : hotdeployEAR
[Dec 24, 2007 7:41:48 AM] Stopped application : hotdeployEAR
[Dec 24, 2007 7:41:48 AM] Undeploy previous deployment
[Dec 24, 2007 7:41:48 AM] Initialize /u01/oracle/product/10.1.3.2/OracleAS_pink/j2ee/pas_oc4j/applications/hotdeployEAR.ear begins...
[Dec 24, 2007 7:41:48 AM] Initialize /u01/oracle/product/10.1.3.2/OracleAS_pink/j2ee/pas_oc4j/applications/hotdeployEAR.ear ends...
[Dec 24, 2007 7:41:48 AM] Starting application : hotdeployEAR
[Dec 24, 2007 7:41:48 AM] Initializing ClassLoader(s)
[Dec 24, 2007 7:41:48 AM] Initializing EJB container
[Dec 24, 2007 7:41:48 AM] Loading connector(s)
[Dec 24, 2007 7:41:49 AM] Starting up resource adapters
[Dec 24, 2007 7:41:49 AM] Processing EJB module: anothermodule.jar
[Dec 24, 2007 7:41:49 AM] application : hotdeployEAR is in failed state
[Dec 24, 2007 7:41:49 AM] Operation failed with error: Missing class: project1.SessionEJBBean Dependent class: com.evermind.server.ejb.deployment.BeanDescriptor Loader: oc4j:10.1.3 Code-Source: /u01/oracle/product/10.1.3.2/OracleAS_pink/j2ee/home/lib/oc4j-internal.jar Configuration: in META-INF/boot.xml in /u01/oracle/product/10.1.3.2/OracleAS_pink/j2ee/home/oc4j.jar This load was initiated at hotdeployEAR.root:0.0.0 using the Class.forName() method. The missing class is not available from any code-source or loader in the system.

The way to achieve this is to use admin_client.jar and then it will work fine. Incremental EJB deployment (updateEjbModule) is not supported in ASC.

Eg: (This will work fine)

D:\jdev\oas-admin-distribution\10132\j2ee\home>java -jar admin_client.jar deployer:oc4j:opmn://papicell-au2.au.oracle.com:6007/pas_oc4j oc4jadmin welcome1 -updateEJBModule -appName hotdeployEAR -ejbModuleName anothermodule.jar -file D:\jdev\jdevprod\10133\jdev\mywork\SR18362234.600\Project1\deploy\anothermodule.jar
07/12/24 07:39:20 Notification ==>Uploading file anothermodule.jar ...

07/12/24 07:39:21 Notification ==>Installation of File anothermodule.jar completed successfully.

D:\jdev\oas-admin-distribution\10132\j2ee\home>