arrayDemo4.sql

Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.7.0

SQL> CREATE OR REPLACE TYPE IDS_QUERY_TYPE AS TABLE OF VARCHAR2(100);
  2  /
Type created

SQL> CREATE OR REPLACE TYPE IDS AS OBJECT ( ids_query IDS_QUERY_TYPE );
  2  /
Type created

SQL> CREATE OR REPLACE TYPE IDS_TABLE AS TABLE OF IDS;
  2  /
Type created

SQL> CREATE OR REPLACE PROCEDURE getInfo(p_ids IN IDS_TABLE) IS
  2  BEGIN
  3     FOR i IN 1 .. p_ids.COUNT LOOP
  4        FOR j IN 1 .. p_ids(i).ids_query.count LOOP
  5           dbms_output.put_line('"' || i || ',' || j || '"=' || p_ids(i).ids_query(j));
  6        END LOOP;
  7     END LOOP;
  8  END getInfo;
  9  /
Procedure created

SQL> CREATE OR REPLACE
  2  AND COMPILE JAVA SOURCE NAMED "ArrayDemo"
  3  as
  4  import java.io.*;
  5  import java.sql.*;
  6  import oracle.sql.*;
  7  import oracle.jdbc.*;
  8
  9  public class ArrayDemo {
 10
 11     public static void passArray() throws SQLException {
 12
 13        Connection conn = new OracleDriver().defaultConnection();
 14
 15        Array idsQuery1 = ((oracle.jdbc.OracleConnection)conn).createARRAY("IDS_QUERY_TYPE", new String[] {"a","b","c"});
 16        Array idsQuery2 = ((oracle.jdbc.OracleConnection)conn).createARRAY("IDS_QUERY_TYPE", new String[] {"d","e","f"});
 17
 18        StructDescriptor idsDescriptor =
 19              StructDescriptor.createDescriptor("IDS",conn);
 20
 21        STRUCT ids1 = new STRUCT (idsDescriptor, conn, new Object[] {idsQuery1});
 22        STRUCT ids2 = new STRUCT (idsDescriptor, conn, new Object[] {idsQuery2});
 23
 24        Array arrayToPass = ((oracle.jdbc.OracleConnection)conn).createARRAY("IDS_TABLE", new STRUCT[] {ids1, ids2});
 25
 26        OraclePreparedStatement ps = (OraclePreparedStatement)conn.prepareStatement ( "begin getInfo(:x); end;" );
 27
 28        ps.setArray( 1, arrayToPass );
 29        ps.execute();
 30
 31     }
 32  }
 33  /
Java created

SQL> CREATE OR REPLACE
  2  PROCEDURE show_java_calling_plsql
  3  AS LANGUAGE JAVA
  4  NAME 'ArrayDemo.passArray()';
  5  /
Procedure created

SQL> exec show_java_calling_plsql ;
"1,1"=a
"1,2"=b
"1,3"=c
"2,1"=d
"2,2"=e
"2,3"=f
PL/SQL procedure successfully completed