Passing an ARRAY from Java to PL/SQL

CREATE OR REPLACE TYPE STRARRAY AS TABLE OF VARCHAR2 (255)
/

CREATE OR REPLACE PACKAGE DEMO_PASSING_PKG
AS
   -- Varchar2's are most easily mapped to the java String type
   PROCEDURE PASS (
      P_IN IN VARCHAR2,
      P_OUT OUT VARCHAR2)
   AS
      LANGUAGE JAVA
         NAME 'demo_passing_pkg.pass( java.lang.String,
                                      java.lang.String[] )';
  
     PROCEDURE PASS (
        P_IN IN STRARRAY,
        P_OUT OUT STRARRAY)
     AS
        LANGUAGE JAVA
           NAME 'demo_passing_pkg.pass_str_array( oracle.sql.ARRAY,
                                               oracle.sql.ARRAY[] )';

   FUNCTION RETURN_STRING
      RETURN VARCHAR2
   AS
      LANGUAGE JAVA
         NAME 'demo_passing_pkg.return_string() return  
           java.lang.String';
END DEMO_PASSING_PKG;
/

 

CREATE OR REPLACE AND COMPILE
JAVA SOURCE NAMED "demo_passing_pkg"
AS
import java.io.*;
import java.sql.*;
import java.math.*;
import oracle.sql.*;
import oracle.jdbc.driver.*;

public class demo_passing_pkg extends Object{

public static void pass( java.lang.String p_in,
                         java.lang.String[] p_out ){

    /*
     * the simplest of datatypes -- the String.  If you remember
     * the C version with 6 formal parameters, null indicators,
     * strlen's, strcpy's and so on -- this is trivial in
     * comparision
     */

    if ( p_in != null ){
        
    System.out.println
        ( "The first parameter is " + p_in.toString() );

        p_out[0] = p_in.toUpperCase();

        System.out.println
        ( "Set out parameter to " + p_out[0].toString() );
    }
}

private static void show_array_info( oracle.sql.ARRAY p_in )
throws SQLException{

    System.out.println( "Array is of type      " +
                         p_in.getSQLTypeName() );
    System.out.println( "Array is of type code " +
                         p_in.getBaseType() );
    System.out.println( "Array is of length    " +
                         p_in.length() );
}

public static void
pass_str_array( oracle.sql.ARRAY p_in, oracle.sql.ARRAY[] p_out )
throws java.sql.SQLException,IOException{

    show_array_info( p_in );
    String[] values = (String[])p_in.getArray();

    for( int i = 0; i < p_in.length(); i++ )
        System.out.println( "p_in["+i+"] = " + values[i] );

    Connection conn = new OracleDriver().defaultConnection();
    ArrayDescriptor descriptor =
       ArrayDescriptor.createDescriptor( p_in.getSQLTypeName(), conn );

    p_out[0] = new ARRAY( descriptor, conn, values );

}

public static String return_string(){
    return "Hello World";
}

}

 

SET serveroutput on size 1000000
EXEC dbms_java.set_output( 1000000 )

DECLARE
   L_IN                          STRARRAY := STRARRAY ();
   L_OUT                         STRARRAY := STRARRAY ();
BEGIN
   FOR I IN 1 .. 5
   LOOP
      L_IN.EXTEND;
      L_IN (I) := 'Element ' || I;
   END LOOP;

   DEMO_PASSING_PKG.PASS (L_IN, L_OUT);

   FOR I IN 1 .. L_OUT.COUNT
   LOOP
      DBMS_OUTPUT.PUT_LINE ('l_out('|| I || ') = ' || L_OUT (I) );
   END LOOP;
END;
/

 

 

 

o STRARRAY is simply our Oracle type that represents the array (collection) we want to 
pass.

o DEMO_PASSING_PKG is our "binding" to the java.  Maps the SQL types to the Java types.

o when you run the PLSQL block at the bottom of your question, we are calling the code:

public static void
pass_str_array( oracle.sql.ARRAY p_in, oracle.sql.ARRAY[] p_out )
throws java.sql.SQLException,IOException{

    show_array_info( p_in );
    String[] values = (String[])p_in.getArray();

    for( int i = 0; i < p_in.length(); i++ )
        System.out.println( "p_in["+i+"] = " + values[i] );

    Connection conn = new OracleDriver().defaultConnection();
    ArrayDescriptor descriptor =
       ArrayDescriptor.createDescriptor( p_in.getSQLTypeName(), conn );

    p_out[0] = new ARRAY( descriptor, conn, values );

}

That code just 

1) dumps the array meta data -- type name, length and so on.
2) gets the array of java strings from the parameter (p_in.getArray())
3) prints out each string in turn (system.out.println)
4) creates a new array to be returned (first half of example shows how to PASS IN, second 
half shows how to pass OUT).  
5) Then, it copies the values in the array we want to return into the OUT parameter.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:3696816290928