not possible to pass array (of BINARY_DOUBLE) from Java to Oracle stored procedure?

Question

Using Java 1.7 and Oracle 11.2 database (with Glassfish 3.1.2), I need to get lots of java float and double arrays into an Oracle stored procedure. Has anyone done this before?

Everything works fine if I try to pass an integer array, as follows:

create or replace TYPE TYPE_ARRAY_INT AS TABLE OF NUMBER; -- array of int
create or replace TYPE TYPE_ARRAY_SINGLE AS TABLE OF BINARY_FLOAT; -- array of single precision (e.g. float)
create or replace TYPE TYPE_ARRAY_DOUBLE AS TABLE OF BINARY_DOUBLE; -- array of double precision (e.g. double)

----ORACLE STORED PROCEDURE----
create or replace procedure SAVE_DATA (my_array IN TYPE_ARRAY_INT)
  as
  begin
     NULL;
  end SAVE_DATA;

 ----JAVA----
 public String SaveData() throws Exception {
   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(?)}");
       ARRAY myArray_orcl = new ARRAY(des_int, conn, intArray);
      cs.setArray(1, myArray_orcl);
       cs.execute();
    } catch (Exception e) {
    } Finally {
          conn.close();
    }
    return "0";
  }

But if I modify the above code to pass either BINARY_FLOAT or BINARY_DOUBLE, I get the error java.sql.SQLException: Internal Error: Array is in inconsistent status:

----ORACLE STORED PROCEDURE----
create or replace procedure SAVE_DATA (my_array IN TYPE_ARRAY_DOUBLE)
  as
  begin
     NULL;
  end SAVE_DATA;

 ----JAVA----
 public String SaveData() throws Exception {
   double[] doubleArray = new double[]{1,2,3};  // create double 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_double=ArrayDescriptor.createDescriptor("TYPE_ARRAY_DOUBLE", conn);
       cs = conn.prepareCall("{call save_data(?)}");
       ARRAY myArray_orcl = new ARRAY(des_double, conn, doubleArray);
      cs.setArray(1, myArray_orcl);
       cs.execute();
    } catch (Exception e) {
    } Finally {
          conn.close();
    }
    return "0";
  }

Anyone know why this occurs? Or a workaround?

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

As an alternative, I tried substituting:

cs.setPlsqlIndexTable(1, doubleArr, doubleArr.length, doubleArr.length, OracleTypes.BINARY_DOUBLE, 0);

but this led to the runtime error:

java.sql.SQLException: Invalid PL/SQL Index Table element type

which I don't understand, since BINARY_DOUBLE is a valid OracleTypes (). http://docs.oracle.com/cd/E11882_01/appdev.112/e13995/oracle/jdbc/OracleTypes.html

Lastly, I eliminated Glassfish (and it's JDBC connection pool) from the equation by substituting:

  OracleDataSource ods = new OracleDataSource();
  ods.setURL("jdbc:oracle:thin:id/pwd@192.168.xxx.xxx:nnnn:sid");
  conn = (OracleConnection) ods.getConnection();

but I still observed the same (initial) behavior (e.g. Array is in inconsistent status error).

score 2 · Answer 1 · answered Jul 16 '15 at 19:46

Try passing the array in as an array of BINARY_DOUBLE objects instead of doubles. The class BINARY_DOUBLE is in the oracle.sql package.

Converting the double[] array to a BINARY_DOUBLE[] array is straightforward:

   BINARY_DOUBLE[] binDoubles = new BINARY_DOUBLE[doubleArray.length];
   for (int i = 0; i < doubleArray.length; ++i) {
       binDoubles[i] = new BINARY_DOUBLE(doubleArray[i]);
   }

Once you've done this, create your ARRAY from binDoubles instead of doubleArray.

I was able to reproduce your error with a standalone console program that connected to the database using plain JDBC and then tried to call your stored procedure. When I made the change above, the error went away and I was able to call the stored procedure.

The call to cs.setPlsqlIndexTable would fail because this method works only with PL/SQL index-by tables, but your types are nested tables.

not possible to pass array (of BINARY_DOUBLE) from Java to Oracle stored procedure?

1 Answers1