Did you try to add connection unwrapping code as suggested in the link I
referenced?
modjklist_at_comcast.net wrote:
> Hi Marina, the original posting had the error and code (see sections for ***JAVA***, ***TYPE*** and ***STORED PROCEDURE***). I'll copy it here for reference:
>
>
>
>>> 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;
>
> ----- Original Message -----
> From: "Marina Vatkina" <marina.vatkina_at_oracle.com>
> To: users_at_glassfish.java.net
> Sent: Thursday, May 3, 2012 2:43:13 PM
> Subject: Re: getting error passing array from Java to Oracle stored procedure related to GlassFish pool
>
> What exactly did you try and how didn't it work?
>
> -marina
>
> modjklist_at_comcast.net wrote:
>
>> Thanks Marina, I did come across that link in my searches. I tried a few ways to implement unwrapping but couldn't get anything working. Any idea how I would modify the code below to get it working?
>>
>> ----- Original Message -----
>> From: "Marina Vatkina" <marina.vatkina_at_oracle.com>
>> To: users_at_glassfish.java.net
>> Sent: Monday, April 30, 2012 3:09:08 PM
>> Subject: Re: getting error passing array from Java to Oracle stored procedure related to GlassFish pool
>>
>> Try this: http://docs.oracle.com/cd/E18930_01/html/821-2418/giyde.html
>>
>> -marina
>>
>> modjklist_at_comcast.net wrote:
>>
>>
>>> 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.
>>>
>>>
>>>