Passing Arrays between Java and Oracle Procedures

Oracle PLSQL provides powerful bulk (array) insert syntax allowing rows to be loaded very quickly.

Using the FORALL and SAVE EXCEPTIONS keywords, the error handling becomes much better than can be achieved with JDBC batching.

With PLSQL array inserts, if row 50 in a batch of 100 fails to insert, then the other 99 rows can still be successfully inserted. In JDBC batching, this is not the case - if a row in the batch fails, then the remaining rows are not even attempted.

The PLSQL syntax for bulk inserts is like the following:

create or replace procedure( ... )
is
  dml_errors EXCEPTION;
  PRAGMA EXCEPTION_INIT(dml_errors, -24381);
begin
  forall i in 1 .. i_payment_amount.last save exceptions
  insert into payments        (payment_id,
                               payment_amount,
                               payment_date,
                               card_number,
                               expire_month,
                               expire_year,
                               name_on_card
                              )
                      values (
                               payments_seq.nextval,
                               i_payment_amount(i),
                               sysdate,
                               i_card_number(i),
                               i_expire_month(i),
                               i_expire_year(i),
                               i_name_on_card(i)
                              );
exception
  when dml_errors then
    for i in 1 .. SQL%bulk_exceptions.count loop
      dbms_output.putline(SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);
      dbms_output.putline(SQL%BULK_EXCEPTIONS(i).ERROR_CODE);
    end loop;
end;

Here, the variables (such as icardnumber) are actually Oracle collections. If any record fails to insert, the dmlerrors exception will be raised after the statement has completed. Details of the exception will be set in the SQL%BULKEXCEPTIONS collection. The ERRORINDEX will contain the index of the record that failed to insert (ie if row 50 in the batch failed, it will contain 50), and ERRORCODE will contain the error that prevented the row from being inserted.

It is possible to pass arrays from Java to Oracle stored procedures to make use of this powerful feature.

First, create collection types on the database for each datatype you want to pass, eg integer, varchar2, number etc:

create or replace type integer_t is table of integer
/ 

create or replace type varchar2_t is table of varchar2(255)
/

create or replace type number_t is table of number
/

Then create a procedure to do the insert and use the collections as the types of the input variables. Remember to include an output collection to notify the Java program of any rows that failed, eg:

Create or replace procedure insert_payments_a(i_payment_amount   number_t,
                                              i_card_number      varchar2_t,
                                              i_expire_month     varchar2_t,
                                              i_expire_year      varchar2_t,
                                              i_name_on_card     varchar2_t,
                                              o_errors       out integer_t)
is
...

Java Code

Java requires an array descriptor object for each collection type on the database:

ArrayDescriptor oracleVarchar2Collection =
    ArrayDescriptor.createDescriptor("VARCHAR2_T",conn);

ArrayDescriptor oracleIntegerCollection =
    ArrayDescriptor.createDescriptor("INTEGER_T",conn);

ArrayDescriptor oracleNumberCollection =
  ArrayDescriptor.createDescriptor("NUMBER_T",conn);

Next you create a callable statement to make the calls to the procedure:

CallableStatement stmt = 
    conn.prepareCall("{ call insert_payments_a(?, ?, ?, ?, ?, ? ) }"); 

Now fill some standard Java array with the data to be inserted. Then you must cast the Java arrays into Oracle arrays using the following code. Note that the relevant descriptor object, oracle connection object and the Java array need to be used in this cast.

ARRAY ora_payment_amount = 
    new ARRAY (oracleNumberCollection,   conn, payment_amount_array);

These Oracle arrays can now be bound to the callable statement, eg:

stmt.setObject(1, ora_payment_amount);
...

The final trick, is working out how to bind the output array from the stored procedure:

stmt.registerOutParameter(6, OracleTypes.ARRAY, "INTEGER_T");

All that is left is to execute the statement and pull back the errors array. The code below puts it all together, with the commented section at the top giving the code to create the Oracle objects the Java code needs to run.

/*
create table payments (payment_id     integer not null,
                       payment_amount number,
                       payment_date   date,
                       card_number    varchar2(20),
                       expire_month   varchar2(2),
                       expire_year    varchar2(2),
                       name_on_card   varchar2(50)
                       );

create unique index payments_idx1 on payments(payment_id);

alter table payments add constraint payments_pk
  primary key (payment_id) using index;

create sequence payments_seq
start with 1
increment by 1
cache 10000;


create or replace type integer_t is table of integer
/

create or replace type varchar2_t is table of varchar2(255)
/

create or replace type number_t is table of number
/

Create or replace procedure insert_payments_a(i_payment_amount   number_t,
                                              i_card_number      varchar2_t,
                                              i_expire_month     varchar2_t,
                                              i_expire_year      varchar2_t,
                                              i_name_on_card     varchar2_t,
                                              o_errors       out integer_t)
is
 dml_errors EXCEPTION;
 PRAGMA EXCEPTION_INIT(dml_errors, -24381);
begin
  -- need to initialize the collection or it will fail if it is used
  o_errors := INTEGER_T();
  forall i in 1 .. i_payment_amount.last save exceptions
  insert into payments        (payment_id,
                               payment_amount,
                               payment_date,
                               card_number,
                               expire_month,
                               expire_year,
                               name_on_card
                              )
                      values (
                               payments_seq.nextval,
                               i_payment_amount(i),
                               sysdate,
                               i_card_number(i),
                               i_expire_month(i),
                               i_expire_year(i),
                               i_name_on_card(i)
                              );
exception
  when dml_errors then
    for i in 1 .. SQL%bulk_exceptions.count loop
      --if SQL%BULK_EXCEPTIONS(i).ERROR_CODE != 00001 then
      --  debug.f( '  ... An unexpected exception occurred (%s)', SQLERRM );
      --  raise;
      --end if;
      o_errors.extend(1);
      o_errors(o_errors.count) := SQL%bulk_exceptions(i).ERROR_INDEX;
    end loop;
end insert_payments_a;
/

*/

import oracle.jdbc.*;
import oracle.sql.ArrayDescriptor;
import oracle.sql.ARRAY;
import oracle.jdbc.OracleTypes;
import java.sql.*;

public class TestArrayProc {

    public static void main(String[] args)
    throws ClassNotFoundException, SQLException
    {
        DriverManager.registerDriver (new oracle.jdbc.OracleDriver());

        String url = "jdbc:oracle:thin:@//localhost:1521/local11gr2.world";

        Connection conn =
        DriverManager.getConnection(url,"sodonnel","sodonnel");

        conn.setAutoCommit(false);

        // Create descriptors for each Oracle collection type required
        ArrayDescriptor oracleVarchar2Collection =
            ArrayDescriptor.createDescriptor("VARCHAR2_T",conn);

        ArrayDescriptor oracleIntegerCollection =
            ArrayDescriptor.createDescriptor("INTEGER_T",conn);

        ArrayDescriptor oracleNumberCollection =
            ArrayDescriptor.createDescriptor("NUMBER_T",conn);

        CallableStatement stmt = 
            conn.prepareCall("{ call insert_payments_a(?, ?, ?, ?, ?, ? ) }"); 

        // JAVA arrays to hold the data.
        double[] payment_amount_array = new double[100];
        String[] card_number_array    = new String[100];
        String[] expire_month_array   = new String[100];
        String[] expire_year_array    = new String[100];
        String[] name_on_card_array   = new String[100];

        // Fill the Java arrays.
        for (int i=0; i< 100; i++) {
            payment_amount_array[i]    = 99.99;
            card_number_array[i]       = "1234567890123456";
            expire_month_array[i]      = "12";
            expire_year_array[i]       = "15";
            name_on_card_array[i]     = "Mr S ODONNELL";
        }

        payment_amount_array[50] = 1001.00;

        // Cast the Java arrays into Oracle arrays
        ARRAY ora_payment_amount = new ARRAY (oracleNumberCollection,   conn, payment_amount_array);
        ARRAY ora_card_number    = new ARRAY (oracleVarchar2Collection, conn, card_number_array);
        ARRAY ora_expire_month   = new ARRAY (oracleVarchar2Collection, conn, expire_month_array);
        ARRAY ora_expire_year    = new ARRAY (oracleVarchar2Collection, conn, expire_year_array);
        ARRAY ora_name_on_card   = new ARRAY (oracleVarchar2Collection, conn, name_on_card_array);

        // Bind the input arrays.
        stmt.setObject(1, ora_payment_amount);
        stmt.setObject(2, ora_card_number);
        stmt.setObject(3, ora_expire_month);
        stmt.setObject(4, ora_expire_year);
        stmt.setObject(5, ora_name_on_card);

        // Bind the output array, this will contain any exception indexes.
        stmt.registerOutParameter(6, OracleTypes.ARRAY, "INTEGER_T");

        stmt.execute();

        // Get any exceptions. Remember Oracle arrays index from 1,
        // so all indexes are +1 off.
        int[] errors = new int[100];
        ARRAY ora_errors = ((OracleCallableStatement)stmt).getARRAY(6);
        // cast the oracle array back to a Java array
        // Remember, Oracle arrays are indexed from 1, while Java is indexed from zero
        // Any array indexes returned as failures from Oracle need to have 1 subtracted from them
        // to reference the correct Java array element!
        errors = ora_errors.getIntArray();
        System.out.println(errors.length);
        System.out.println(errors[0]);
        conn.commit();
    }
}