Java to Oracle multi-dimensional array example

This Java code creates a multi-dimensional (2d) array and passes it to an Oracle PL/SQL Stored Procedure as varray or varrays which then iterates the multidimensional varray collections and inserts the data into a table.

Oracle 11g R2 (11.2) Documentation

package oracleMultiDimensionalArrays;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;

public class Test
{
  public static void send() throws SQLException
  {
    DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
    Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@dbIP:1521:SID", "troy_a", "troy_a");

    ArrayDescriptor desc = ArrayDescriptor.createDescriptor("VARRAY2", conn);

    int[][] elements = {{11, 12}, {21, 22}, {31, 32}};
    ARRAY array2 = new ARRAY(desc, conn, elements);

    CallableStatement cstmt = conn.prepareCall("{ call varraytest (?) }");
    cstmt.setArray(1, array2);
    cstmt.execute();
  }

  public static void main(String args[]) throws Exception
  {
    send();
  }
}


CREATE TYPE varray1 AS VARRAY(10) OF NUMBER(38);
CREATE TYPE varray2 AS VARRAY(10) OF varray1;

create table foo (
  id NUMBER(38)
);

create or replace procedure varraytest (
   array2 in varray2
) AS
   array1 varray1;
   elem NUMBER;
BEGIN
  for i in 1 .. array2.count loop
    array1 := array2(i);
    for j in 1 .. array1.count loop
      elem := array1(j);
      insert into foo(id) values (elem);
    end loop;
  end loop;
END;
/