Passing Record Types between Oracle and Java

Related to the post on on passing arrays between java and oracle, someone asked me how to pass record types between Java and Oracle. Largely the same approach is used, but it is different enough to warrant an example.

First, create the record type you wish to use:

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

Then create a procedure or function to send it to. In this case, I have a procedure that simply copies the passed in record into an output record, which is enough to prove the values are being passed in and out of the procedure correctly:

create or replace procedure p_rec (v1 rectype, v2 out rectype)
is
begin
  v2 := v1;
end;
/

That is all you need on the Oracle side, so the tricky bit, is writing the Java code to call this procedure:

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:@//localhost:1521/local11gr2.world";

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

        conn.setAutoCommit(false);

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

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

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

        // put some values in the array

        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);
        // This struct is used to hold the return record type 
        STRUCT output_oracle_record;

        // Bind the input record
        stmt.setObject(1, oracle_record);

        // register the output parameter
        stmt.registerOutParameter(2, OracleTypes.STRUCT, "RECTYPE");

        stmt.execute();

        // get the returned record from the callable statement - note the cast on the stmt handle    
        output_oracle_record = ((OracleCallableStatement)stmt).getSTRUCT(2);

        // finally cast the Oracle struct back into a Java array
        return_record_array = output_oracle_record.getAttributes();

        // Show the results:
        System.out.println("First Object is now "+return_record_array[0]+" and "+return_record_array[1]);
    }
}

That is all there is too it. If I get some more free time, I will have a go at passing an array of records to a procedure, which I imagine is a combination of this code and the code to pass arrays to a procedure.