1

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/[email protected]:nnnn:sid");
  conn = (OracleConnection) ods.getConnection();

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

1 Answer 1

2

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.

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.