Passing arrays of record types between Oracle and Java

A while back, I needed to figure out how to pass arrays of values into Oracle to harness the power of array inserts. Later I got a question from a reader asking passing in arrays of record types. I figured it was best to answer this by first figuring out how to pass a single record type to a procedure.

The final piece of the puzzle is bringing together the array and record approach to allow passing in an array of records.

Firstly, you need to create two types on the database:

create or replace type rectype as object(col1 varchar2(10),col2 varchar2(10));
/ 
create or replace type rectypetab as table of rectype;
/

Then we need a simple procedure to pass the array to:

create table t (c1 varchar2(255));

create or replace procedure p_rec (v1 rectypetab)
is
begin
  for i in 1..v1.count loop
      insert into t values(v1(i).col1);
      insert into t values(v1(i).col2);
  end loop;
end; 
/

This procedure will loop over the array, and for each record found it will insert each of its fields into the table. Probably not very useful, but the code is simple and is good enough for a demonstration.

Now for the Java code. If you understand the previous examples, this code should make a lot of sense.

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

public class Rec {

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

        String url = "jdbc:oracle:thin:@//192.168.0.1:1521/tuned.home";

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

        conn.setAutoCommit(false);

        // Create descriptors for each Oracle record type required
        StructDescriptor recDescriptor = 
          StructDescriptor.createDescriptor("RECTYPE",conn);

        // create a descriptor for the array of records
        ArrayDescriptor arrayDescriptor = 
        ArrayDescriptor.createDescriptor("RECTYPETAB", conn);

        // create a callable statement to make the call to the proc
        CallableStatement stmt = 
            conn.prepareCall("{ call p_rec(?) }");

        // Declare a java array of objects to form the basis of our array to pass to the
        // procedure
        Object[] array_of_records = new Object[5];

        // In java, you stage the values for each field in the Oracle record in an array
        Object[] java_record_array   = new Object[2];

        // loop 5 time to create 5 records and put each into an array, giving us
        // 5 records in total.
        for (int i=0; i< 5; i++) {
            // We create a record by filling an array and then casting it
            // into an Oracle type
            java_record_array[0] = "col1";
            java_record_array[1] = "col2";

            // cast the java arrays into the Oracle record type for the input record
            STRUCT oracle_record = new STRUCT(recDescriptor, conn, java_record_array);
            // store the oracle_record into the array of records which
            // will be passed to the function
           array_of_records[i] = oracle_record;
        }

        // now we need to cast the Java array of records into an Oracle array
        ARRAY oracle_array = new ARRAY(arrayDescriptor, conn, array_of_records);

        // Bind the input record
        stmt.setObject(1, oracle_array);
        stmt.execute();
        conn.commit();
    }
}

Again, it is pretty simple ... once you have it all worked out!