Fetching an pl/sql array (as out parameter)

Question

I have an Oracle stored procedure with an array as input parameter and an array as output parameter. While the input parameter already works fine, I always get back an array of null-values (although the length of the array is what I expected).

It is only a test environment so it is a trivial example: the stored procedure only takes the input array and copy the values to the output array and to a varchar2 field, so I can see that the copy from the input array to the varchar2 field works fine but not to the output-array.

My Java Code is the following:

    DriverManager.registerDriver(new OracleDriver());
    Connection conn = DriverManager.getConnection(
            "<ConnectionString>", "<user>", "<password>");
    conn.setAutoCommit(false);
    OracleConnection oracleConnection = (OracleConnection)conn;

    OracleCallableStatement stmt = (OracleCallableStatement)oracleConnection.prepareCall("call MYPACKAGE.TABLE_IN_TABLE_OUT( ?, ?, ? )");

    String[] inputStringArray = { "1", "2", "3", "4" };
    Array inputArray = oracleConnection.createOracleArray("MYPACKAGE.CHAR_TABLE", inputStringArray);

    stmt.setArray(1, inputArray);
    stmt.registerOutParameter(2, Types.ARRAY, "MYPACKAGE.ERG_TABLE");
    stmt.registerOutParameter(3, Types.VARCHAR);
    stmt.executeUpdate();

    Array resultArray = stmt.getArray(2);
    String [] resultStringArray = (String[])resultArray.getArray();
    String resultString = stmt.getString(3);

    System.out.println(resultString);
    for (String result : resultStringArray) {
        System.out.println(result);
    }

    conn.commit();
    conn.close();

The stored procedure

create or replace PACKAGE MYPACKAGE IS 
TYPE CHAR_TABLE IS TABLE OF CHAR(01) INDEX BY BINARY_INTEGER;
TYPE ERG_TABLE IS TABLE OF CHAR(01) INDEX BY BINARY_INTEGER;


PROCEDURE TABLE_IN_TABLE_OUT(
    inputArray  IN    CHAR_TABLE,
    outputArray  OUT   ERG_TABLE,
    resultString OUT VARCHAR2
);
END MYPACKAGE;

The implementation of the stored procedure:

create or replace PACKAGE BODY MYPACKAGE AS
  PROCEDURE TABLE_IN_TABLE_OUT(
     inputArray  IN    CHAR_TABLE,
     outputArray  OUT   ERG_TABLE,
    resultString OUT VARCHAR2) AS
  BEGIN
    FOR i IN 0..inputArray.last  loop
      outputArray(i) := inputArray(i);       
    end loop;
    resultString := '';
    FOR i IN 0..outputArray.last loop
      resultString := resultString || outputArray(i);
    end loop;
  END TABLE_IN_TABLE_OUT;
END MYPACKAGE;

And this is the output:

VARCHAR2 result: 1234
Array result: null, null, null, null,  

After searching a lot on the internet and in this forum I really did not find out what I am doing wrong.

Answer 1

Finally (after hours of researching) I found also the way to return index-by Tables which works now. Since it was a very painful way I want to share my solution here:

    DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
    Connection conn = DriverManager.getConnection(
            "<connectionString>", "<user>", "<password>");
    conn.setAutoCommit(false);
    OracleConnection oracleConnection = (OracleConnection)conn;
    OracleCallableStatement stmt = (OracleCallableStatement)oracleConnection.prepareCall("BEGIN MYPACKAGE.TABLE_IN_TABLE_OUT( ?, ?, ? ); END;");

    String[] inputStringArray = { "1", "2", "3", "4", "5", "6"};
    Array inputArray = oracleConnection.createOracleArray("MYPACKAGE.CHAR_TABLE", inputStringArray);

    stmt.setArray(1, inputArray);
    stmt.registerIndexTableOutParameter(2, 100, OracleTypes.VARCHAR, 100);
    stmt.registerOutParameter(3, Types.VARCHAR);
    stmt.execute();

    String resultString = stmt.getString(3);
    String[] resultArray = (String[])stmt.getPlsqlIndexTable(2);

    System.out.println("VARCHAR2 result: " + resultString);
    System.out.print("Array result: ");
    for (String result : resultArray) {
        System.out.print(result + ", ");
    }

The most important things that I have changed:

1. I changed the Call String from:

   oracleConnection.prepareCall("call MYPACKAGE.TABLE_IN_TABLE_OUT( ?, ?, ? )");
   

   to

   oracleConnection.prepareCall("BEGIN MYPACKAGE.TABLE_IN_TABLE_OUT( ?, ?, ? ); END;");
   

   because I was running into an "ORA-01484: array can only be bound to PL/SQL statements" when using the Method "registerIndexTableOutParameter" (see next point).

2. Instead of registering the Array this way:

     stmt.registerOutParameter(2, Types.ARRAY, "MYPACKAGE.ERG_TABLE");
   

   i do it this way now:

    stmt.registerIndexTableOutParameter(2, 100, OracleTypes.VARCHAR, 100);
   

3. To get the array I had to use the following code:

   String[] resultArray = (String[])stmt.getPlsqlIndexTable(2);
   

And thats all. Hopefully this helps others.