Passing Arrays of Objects between Java and PLSQL

Recently I had to transfer a lot of tabular data to a PLSQL procedure from Java. The most obvious answer seemed to be to represent the data as an array of records. Searched on the net to find this example solution.

On the Java side, Records are plain java classes. On SQL side, the java objects are mapped to a corresponding SQL Object type. The whole thing is passed as an Array (Table in SQL).


CODE FOLLOWS -

create or replace and compile java source named Person as

  import java.sql.*;
  import java.io.*;

  public class Person implements SQLData
     {
       private String sql_type = "PERSON_T";

       public int person_id;
       public String person_name;

       public Person () {}

       public String getSQLTypeName() throws SQLException { return sql_type; }

       public void readSQL(SQLInput stream, String typeName) throws SQLException
       {
         sql_type = typeName;
         person_id = stream.readInt();
         person_name  = stream.readString();
       }

       public void writeSQL(SQLOutput stream) throws SQLException
       {
          stream.writeInt (person_id);
          stream.writeString (person_name);
       }

     }
CREATE TYPE person_t AS object
  EXTERNAL NAME 'Person' LANGUAGE JAVA
  USING SQLData (
    person_id NUMBER(9) EXTERNAL NAME 'person_id',
    person_name VARCHAR2(30) EXTERNAL NAME 'person_name'
  )

CREATE TYPE person_tab IS table OF person_t;

create or replace and compile java source named arraydemo as
  import java.util.Date;
  import java.io.*;
  import java.sql.*;
  import oracle.sql.*;
  import oracle.jdbc.driver.*;
  import java.text.DateFormat;
  import java.text.ParseException;
  import java.text.SimpleDateFormat;

  public class ArrayDemo
  {

     public static void passArray() throws SQLException
     {
        Connection conn = new OracleDriver().defaultConnection();

        ArrayDemo a = new ArrayDemo();

        Person pn1 = new Person();
        pn1.person_id = 1;
        pn1.person_name = "TestName1";
      
        Person pn2 = new Person();
        pn2.person_id = 2;
        pn2.person_name = "TestName2";

        Person pn3 = new Person();
        pn3.person_id = 31;
        pn3.person_name = "TestName3";
      
       Person[] P_arr = {pn1, pn2, pn3};

   ArrayDescriptor descriptor =
       ArrayDescriptor.createDescriptor( "PERSON_TAB", conn );

    ARRAY array_to_pass =
       new ARRAY( descriptor, conn, P_arr);

    OraclePreparedStatement ps =
        (OraclePreparedStatement)conn.prepareStatement
        ( "begin give_me_an_array(:x); end;" );

    ps.setARRAY( 1, array_to_pass );
    ps.execute();
  }
}


create or replace
procedure give_me_an_array( p_array in person_tab )
as
begin
    for i in 1 .. p_array.count
    loop
       dbms_output.put_line('Person Id:'|| p_array(i).person_id||' Name: '||p_array(i).person_name);
   end loop;
end;

create or replace
procedure show_java_calling_plsql
as language java
name 'ArrayDemo.passArray()';


set serveroutput on

exec show_java_calling_plsql