I'm guessing GlassFish's JDBC connection pool is wrapping the JDBC driver classes and proxying them seamlessly, which is causing the problem. How do others get around this so that Oracle specific features may be used?
Most of my Java methods work fine as is, but I need one Java method to use a specific Oracle feature. Is that possible using a JDBC Connection pool with GlassFish?
----- Original Message -----
From: modjklist_at_comcast.net
To: users_at_glassfish.java.net
Sent: Thursday, April 26, 2012 3:11:21 PM
Subject: Re: getting error passing array from Java to Oracle stored procedure related to GlassFish pool
Some additional info: When I read the type of connection I have it returns:
com.sun.gjc.spi.jdbc40.ConnectionWrapper40_at_31c74456
instead of what I expected to see, which was some sort of Oracle JDBC connection.
Is this a bug?
https://bugs.eclipse.org/bugs/show_bug.cgi?id=300568
----- Original Message -----
From: modjklist_at_comcast.net
To: users_at_glassfish.java.net
Sent: Thursday, April 26, 2012 1:08:33 PM
Subject: getting error passing array from Java to Oracle stored procedure related to GlassFish pool
I'm trying to pass some arrays from Java to an Oracle 11.2 stored procedure and I'm getting a runtime error. The error I'm seeing is:
java.lang.ClassCastException : com.sun.gjc.spi.jdbc40.ConnectionWrapper40 cannot be cast to oracle.jdbc.OracleConnection
I'm compiling this code:
********JAVA**********
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import javax.sql.DataSource;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import oracle.jdbc.OracleTypes;
import oracle.sql.*;
import oracle.jdbc.driver.*;
public class myClass {
public String SR(String[] rl, String[] rn, int[] cn) throws Exception {
Connection conn;
CallableStatement cs;
Context context = new InitialContext();
DataSource ds = (DataSource)context.lookup("jdbc/dbop");
conn = ds.getConnection();
ArrayDescriptor str_descriptor = ArrayDescriptor.createDescriptor("TYPE_STRING", conn);
ArrayDescriptor num_descriptor = ArrayDescriptor.createDescriptor("TYPE_NUMBER", conn);
ARRAY rla = new ARRAY(str_descriptor, conn, rl);
ARRAY rna = new ARRAY(str_descriptor, conn, rn);
ARRAY cna = new ARRAY(num_descriptor, conn, cn);
cs = conn.prepareCall( "{call myproc (?,?,?)}" );
cs.setArray( 1, rla);
cs.setArray( 2, rna);
cs.setArray( 3, cna);
cs.execute();
if ( conn != null ) {
try { conn.close(); } catch ( Exception ex ) {}
conn = null;
}
}
}
where the database has:
********TYPE**********
create or replace TYPE type_number AS table OF NUMBER(2,0);
create or replace TYPE type_string AS table OF VARCHAR2(150);
and
********STORED PROCEDURE**********
create or replace procedure myproc (
in_rla IN type_string,
in_rna IN type_string,
in_cna IN type_number)
as
begin
FOR ii IN 1.. 7
LOOP
INSERT INTO mytable (col_cn, col_rn, col_rl)
VALUES (in_cna(ii), in_rna(ii), in_rla(ii));
END LOOP;
COMMIT;
END myproc;
It's my first attempt at passing in arrays from java to stored procedure. Any comments appreciated.