Handling Oracle Stored Procedures In Glassfish Server

Recently we had a requirement where we need to call a stored procedure which internally does some internal DDL inserts.

create or replace TYPE tab_aa IS TABLE OF rec_aa;

create or replace TYPE rec_aa IS OBJECT (

   attribute_key   VARCHAR2(100)

   ,attribute_value   VARCHAR2(1024)

   );
 PROCEDURE createRegistrationProc(
      in_account_no   IN   VARCHAR2
      ,in_site_id   IN   NUMBER
      ,in_campaign_code   IN   VARCHAR2
      ,in_attribute_pair   IN   tab_aa     --  An array of attribute key/value pairs
      ,out_result   OUT   INTEGER
      )

We have no issues of calling the stored procedure passing oracle type array as one of parameter and data getting inserted successfully into database when we try to retrieve the connection using OracleDriver as shown below.

String driver = "oracle.jdbc.driver.OracleDriver"; Class driverClass = Class.forName(driver); connection = DriverManager.getConnection(url, user, password);


We started getting issues when we try to retrieve Connection dependent on Glassfish container as shown below.
final Context context = new InitialContext(); final DataSource dataSource = (DataSource) context.lookup(configuration.getString("Example.DataSource"));
Issue Occured


Caused by: java.sql.SQLException: java.lang.ClassCastException: com.sun.gjc.spi.jdbc40.ConnectionHolder40 cannot be cast to oracle.jdbc.OracleConnection Caused by: java.lang.ClassCastException: com.sun.gjc.spi.jdbc40.ConnectionHolder40 cannot be cast to oracle.jdbc.OracleConnection at oracle.sql.TypeDescriptor.setPhysicalConnectionOf(TypeDescriptor.java:609) at oracle.sql.TypeDescriptor.(TypeDescriptor.java:142) at oracle.sql.StructDescriptor.(StructDescriptor.java:325)


Note: The above issue is caused since we pass oracle.sql.ArrayDescriptor as one of the parameter to the stored procedure. Since Glassfish server internally create com.sun.gjc.spi.jdbc40.ConnectionHolder40 as the Database Connection object which is imcompatible for usage for oracle.sql.ArrayDescriptor.
Solution:

There are 2 steps to be followed inorder to solve this issue.
Step 1: Whenever we handle Oracle Stored Procedures in a Glassfish container its always suggested to create JDBC connection pool with below settings.


Resource Type: javax.sql.ConnectionPoolDataSource
Datasource ClassName: oracle.jdbc.pool.OracleConnectionPoolDataSource

Usage of javax.sql.XADataSource instead of ConnectionPoolDataSource was one of the major issue behind the problem.
Step 2: Inorder to avoid the class-cast-exception issue as mentioned above we have 2 options.

Step 2 Solution a : Unwrap the existing connection and typecast to OracleConnection object.
OracleConnection oraCon = connection.unwrap(OracleConnection.class);


Step 2 Solution b : Make sure we type cast the Connection object to Glassfish proiertary classes as shown below.As we can see that the original connection object is used for prepareCall statement and the rest of oracle array creation depends on the 2nd connection object.More information regarding Solution b can be found here Oracle Docs




com.sun.appserv.jdbc.DataSource dataSource = (com.sun.appserv.jdbc.DataSource) context.lookup(configuration.getString("Example.DataSource"));
Connection connection = dataSource.getConnection();
Connection driverManagerConnection=driverManagerConnection=dataSource.getConnection(connection);
CallableStatement callableStatement = con.prepareCall(....);
StructDescriptor strctDescriptor = strctDescriptor = StructDescriptor.createDescriptor("REC_CODES", driverManagerConnection);
ArrayDescriptor arrayDescriptor = ArrayDescriptor.createDescriptor("TAB_CODES", driverManagerConnection);
Since Step 2 Solution a does not depend on any glassfish proiertary classes hence it is suggested to chose Option a over Option b.