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.