how to pass array from java to Oracle stored procedure using JDBC connection pool in Glassfish?

tem

Using java 1.7.0_25 with Glassfish 3.1 and Oracle 11.2 database.

There are many examples online to pass an array from Java to an Oracle stored procedure. But throw Glassfish into the mix to use a JDBC connection pool, and those examples no longer work. The following describes the error I see:

Currently I have Glassfish admin console's settings set to:

    Resource Type: javax.sql.DataSource

    Datasource Classname: oracle.jdbc.pool.OracleDataSource

I define a type `TYPE_ARRAY_SINGLE` in Schema level of Oracle database (e.g. not inside a package):

   create or replace TYPE TYPE_ARRAY_SINGLE AS TABLE OF BINARY_FLOAT; -- array of single precision (e.g. float)

To establish a baseline, I first have the following code that works (e.g. where no array is passed yet):

    ----ORACLE----

    create or replace procedure SAVE_DATA (

      in_1 IN varchar2,

      out_1 OUT varchar2)

      as

      begin

        out_1='valid';

      end SAVE_DATA;

     ----JAVA----

     public String SaveData(String in1, float[] myArray) throws Exception {

       String out1="";

       Connection conn = null;

       CallableStatement cs=null;

       try {

           Context context = new InitialContext();

           DataSource ds = (DataSource) context.lookup("jdbc/myOraclePool");

           OracleDataSource ods = ds.unwrap(OracleDataSource.class);

           conn = (OracleConnection) ods.getConnection();

           ArrayDescriptor des_single=ArrayDescriptor.createDescriptor("TYPE_ARRAY_SINGLE", conn);

           cs = conn.prepareCall("{call save_data(?,?)}");

           cs.setString(1, in1);

           ARRAY myArray_orcl = new ARRAY(des_single, conn, myArray);

           cs.registerOutParameter(2, Types.VARCHAR);

           cs.execute();

           out1=cs.getString(2);

        } catch (Exception e) {

        } Finally {

              conn.close();

        }

        return out1;

      }

Then I change to above code to pass array `myArray` (see changes below in bold), restart Glassfish, and observe the runtime error: java.sql.SQLException: Internal Error: Array is in inconsistent status,

    create or replace procedure SAVE_DATA (

      in_1 IN varchar2,

      my_array IN TYPE_ARRAY_SINGLE,  -- add array here

      out_1 OUT varchar2)

      as

      begin

        out_1='valid';

      end SAVE_DATA;

     ----JAVA----

     public String SaveData(String in1, float[] myArray) throws Exception {

       String out1="";

       Connection conn = null;

       CallableStatement cs=null;

       try {

           Context context = new InitialContext();

           DataSource ds = (DataSource) context.lookup("jdbc/myOraclePool");

           OracleDataSource ods = ds.unwrap(OracleDataSource.class);

           conn = (OracleConnection) ods.getConnection();

           ArrayDescriptor des_single=ArrayDescriptor.createDescriptor("TYPE_ARRAY_SINGLE", conn);

           cs = conn.prepareCall("{call save_data(?,?,?)}");

           cs.setString(1, in1);

           ARRAY myArray_orcl = new ARRAY(des_single, conn, myArray);

          cs.setArray(2, myArray_orcl);  // add array here

           cs.registerOutParameter(3, Types.VARCHAR);

           cs.execute();

           out1=cs.getString(3);

        } catch (Exception e) {

        } Finally {

              conn.close();

        }

        return out1;

      }

Any ideas why I get a runtime error, and how to resolve? I don't even understand what the error is telling me (and Googling the error doesn't turn up much).

Do I need to grant any special permission to the type TYPE_ARRAY_SINGLE to enable jdbc or java to work with it?

The stack trace error is:

   full stack trace: java.sql.SQLException: Internal Error: Array is in inconsistent status

        at com.example.AClass.SaveData(AClass.java:137)

        at com.example.BClass.uSaveData(BClass.java:9932)

        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)

        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

        at java.lang.reflect.Method.invoke(Method.java:606)

        at flex.messaging.services.remoting.adapters.JavaAdapter.invoke(JavaAdapter.java:418)

        at flex.messaging.services.RemotingService.serviceMessage(RemotingService.java:183)

        at flex.messaging.MessageBroker.routeMessageToService(MessageBroker.java:1400)

        at flex.messaging.endpoints.AbstractEndpoint.serviceMessage(AbstractEndpoint.java:1005)

        at flex.messaging.endpoints.amf.MessageBrokerFilter.invoke(MessageBrokerFilter.java:103)

        at flex.messaging.endpoints.amf.LegacyFilter.invoke(LegacyFilter.java:158)

        at flex.messaging.endpoints.amf.SessionFilter.invoke(SessionFilter.java:44)

        at flex.messaging.endpoints.amf.BatchProcessFilter.invoke(BatchProcessFilter.java:67)

        at flex.messaging.endpoints.amf.SerializationFilter.invoke(SerializationFilter.java:166)

        at flex.messaging.endpoints.BaseHTTPEndpoint.service(BaseHTTPEndpoint.java:291)

        at flex.messaging.MessageBrokerServlet.service(MessageBrokerServlet.java:353)

        at javax.servlet.http.HttpServlet.service(HttpServlet.java:770)

        at org.apache.catalina.core.StandardWrapper.service(StandardWrapper.java:1542)

        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:343)

        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:217)

        at com.example.filters.CharsetFilter.doFilter(CharsetFilter.java:49)

        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:256)

        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:217)

        at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:279)

        at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:175)

        at org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:655)

        at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:595)

        at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:161)

        at org.apache.catalina.connector.CoyoteAdapter.doService(CoyoteAdapter.java:331)

        at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:231)

        at com.sun.enterprise.v3.services.impl.ContainerMapper$AdapterCallable.call(ContainerMapper.java:317)

        at com.sun.enterprise.v3.services.impl.ContainerMapper.service(ContainerMapper.java:195)

        at com.sun.grizzly.http.ProcessorTask.invokeAdapter(ProcessorTask.java:849)

        at com.sun.grizzly.http.ajp.AjpProcessorTask.invokeAdapter(AjpProcessorTask.java:135)

        at com.sun.grizzly.http.ProcessorTask.doProcess(ProcessorTask.java:746)

        at com.sun.grizzly.http.ProcessorTask.process(ProcessorTask.java:1045)

        at com.sun.grizzly.http.DefaultProtocolFilter.execute(DefaultProtocolFilter.java:228)

        at com.sun.grizzly.DefaultProtocolChain.executeProtocolFilter(DefaultProtocolChain.java:137)

        at com.sun.grizzly.DefaultProtocolChain.execute(DefaultProtocolChain.java:104)

        at com.sun.grizzly.DefaultProtocolChain.execute(DefaultProtocolChain.java:90)

        at com.sun.grizzly.http.HttpProtocolChain.execute(HttpProtocolChain.java:79)

        at com.sun.grizzly.ProtocolChainContextTask.doCall(ProtocolChainContextTask.java:54)

        at com.sun.grizzly.SelectionKeyContextTask.call(SelectionKeyContextTask.java:59)

        at com.sun.grizzly.ContextTask.run(ContextTask.java:71)

        at com.sun.grizzly.util.AbstractThreadPool$Worker.doWork(AbstractThreadPool.java:532)

        at com.sun.grizzly.util.AbstractThreadPool$Worker.run(AbstractThreadPool.java:513)

        at java.lang.Thread.run(Thread.java:724)

Furthermore, I queried the SQLException as follows:

    getSQLState() returns 99999

    getErrorCode() returns 17001

I'm not sure what that means though.

tem

UPDATE 1: If I change the above code to pass an integer array (instead of a binary_float array), it works. I've marked the changes in bold font below.

    create or replace TYPE TYPE_ARRAY_INT AS TABLE OF NUMBER; -- array of integers

    create or replace procedure SAVE_DATA (

      in_1 IN varchar2,

      my_array IN TYPE_ARRAY_INT,  -- add array here

      out_1 OUT varchar2)

      as

      begin

        out_1='valid';

      end SAVE_DATA;

     ----JAVA----

     public String SaveData(String in1, float[] myArray) throws Exception {

       String out1="";

       int[] intArray = new int[]{1,2,3};  // create integer array

       Connection conn = null;

       CallableStatement cs=null;

       try {

           Context context = new InitialContext();

           DataSource ds = (DataSource) context.lookup("jdbc/myOraclePool");

           OracleDataSource ods = ds.unwrap(OracleDataSource.class);

           conn = (OracleConnection) ods.getConnection();

           ArrayDescriptor des_int=ArrayDescriptor.createDescriptor("TYPE_ARRAY_INT", conn);

           cs = conn.prepareCall("{call save_data(?,?,?)}");

           cs.setString(1, in1);

           ARRAY myArray_orcl = new ARRAY(des_int, conn, intArray);

          cs.setArray(2, myArray_orcl);  // add array here

           cs.registerOutParameter(3, Types.VARCHAR);

           cs.execute();

           out1=cs.getString(3);

        } catch (Exception e) {

        } Finally {

              conn.close();

        }

        return out1;

      }

Anyone understand why BINARY_DOUBLE and BINARY_FLOAT lead to an Internal Error: Array is in inconsistent status error?

Here's the relevant section for Oracle 11.2 database regarding JDBC support of binary_float and binary_double:

http://docs.oracle.com/cd/E11882_01/java.112/e16548/oraint.htm#JJDBC28153

UPDATE 2: To try to do the same thing in a different way, if I replace:

    ArrayDescriptor des_single=ArrayDescriptor.createDescriptor("TYPE_ARRAY_SINGLE", conn);

    ARRAY myArray_orcl = new ARRAY(des_single, conn, myArray);

    cs.setArray(2, myArray_orcl);  // add array here

with:

    float[] floatArr = new float[]{1,2,3};

    cs.setPlsqlIndexTable(2, floatArr, floatArr.length, floatArr.length, OracleTypes.BINARY_FLOAT, 0);

I get the runtime error: java.sql.SQLException: Invalid PL/SQL Index Table element type

However, BINARY_FLOAT is a valid Oracle Type:

    http://docs.oracle.com/cd/E11882_01/appdev.112/e13995/oracle/jdbc/OracleTypes.html

so I'm not sure why the error occurs. Is this a bug?

UPDATE 3: I can eliminate Glassfish from the equation by replacing:

           DataSource ds = (DataSource) context.lookup("jdbc/myOraclePool");

           OracleDataSource ods = ds.unwrap(OracleDataSource.class);

           conn = (OracleConnection) ods.getConnection();

with:

           OracleDataSource ods = new OracleDataSource();

           ods.setURL("jdbc:oracle:thin:id/[email protected]:nnnn:sid");

           conn = (OracleConnection) ods.getConnection();

and this still produces the same error: java.sql.SQLException: Internal Error: Array is in inconsistent status

Am I the first one to try to pass a BINARY_DOUBLE or BINARY_FLOAT array to an Oracle stored procedure from java?

I really don't want to have to waste resources to convert float and double arrays to a string in Java then convert back to binary in PL/SQL... is there another way?

Thoughts anyone???????????????????????????????????????????????????