How do I get a stored procedure output parameter that is an array to work?

Question

I have been developing Java/JDBC calls to stored procedures with some success. However I am stuck when an output parameter is an array of strings. I have been successful with scalar types as input and output parameters, as well as an array of strings as an input parameter.

Here's the PL/SQL code I have:

TYPE StringArray IS TABLE OF VARCHAR2(32767) INDEX BY BINARY_INTEGER;

create or replace package body 
test is
  procedure upper(
    in_array     in StringArray,
    out_array    out StringArray
  ) is
    tmp StringArray := in_array;
  begin
    for i in 0..(tmp.count-1) loop
      tmp(i) := UPPER(tmp(i));
    end loop;
    out_array := tmp;
  end;
end test;

Here's the Java code I have:

public void testString(Connection connection) {
    String[] values = { "alpha", "beta", "gamma" };
    try {
       CallableStatement callableStatement = connection.prepareCall("begin test.upper(?, ?); end;");
       DelegatingConnection<OracleConnection> delegatingConnection = (DelegatingConnection<OracleConnection>) new DelegatingConnection(connection);
       OracleConnection oracleConnection = (OracleConnection) delegatingConnection.getInnermostDelegate();
       Array input oracleConnection.createOracleArray("STRINGARRAY", values);
       callableStatement.setObject("in_array", input);
       callableStatement.registerOutParameter("out_array", Types.ARRAY, "STRINGARRAY");
       callableStatement.execute();
       Array output = (Array)callableStatement.getObject("out_array");
       String[] result = (String[])output.getArray();
       System.out.println("Length: " + result.length);  // Prints "Length: 3"
       System.out.println("First: " + result[0]);       // Prints "First: null"
    } (catch SQLException e) {
       // Handle error
    }
}

If I call the PL/SQL stored procedure from an SQL script directly it works. So I think the stored procedure itself is okay.

If I call the stored procedure via JDBC it completes normally. Using debug statements, I have confirmed that values is correctly sent from the Java client to the stored procedure into in_array. That is, an array of length 3 with the appropriate values is received. As best as I can tell, out_array is sent back to the Java client. However, something goes wrong. result is of size 3 but all the elements are null.

If I inspect output, I can see that internally it has a byte[] of length 38. Sub-sequences of these bytes map to "ALPHA", "BETA" and "GAMMA". So it looks like the data makes it back to the client but it is not converted into a String[] correctly.

What am I doing wrong?

Where is the actual issue you facing. In PLSQL block or in JAVA call. Its not clear from your question — XING, Dec 08 '17 at 05:48
@XING As best I can tell, the PL/SQL is okay as the stored procedure completes normally. However, in the Java client code, the result is an array of the correct length but with `null` elements. So the data is not being transformed correctly at the client end. Based on this I think the issue is at the Java end, but you never know. — dave, Dec 08 '17 at 05:59
Maybe use vararrays or nested tables instead of associative arrays (index-by-tables). They wrote in [the documentation](https://docs.oracle.com/database/121/JJDBC/oraarr.htm#JJDBC28574) that their JDBC driver supports varrarrays and nested tables, index-by-tables are nowhere mentioned in the documentation. — krokodilko, Dec 08 '17 at 07:06
What makes the arrays associative? Is it the binary indexing? — dave, Dec 08 '17 at 20:37
Yes. `table of x index by y` is a PL/SQL associative array type. I don't know Java but the SQL-compatible collection types may give you more options. — William Robertson, Dec 09 '17 at 08:41

MT0 · Accepted Answer · 2017-12-08T10:14:22.343

Do not use an Associative Array - use a Collection:

CREATE TYPE StringArray IS TABLE OF VARCHAR2(4000);
CREATE TYPE CLOBArray   IS TABLE OF CLOB;

Then you can do:

public void testString(Connection connection) {
  String[] values = { "alpha", "beta", "gamma" };
  try {
    OracleConnection oc = (OracleConnection) connection;

    ARRAY stringArray = oc.createARRAY( "STRINGARRAY", values ); // Upper case identifier

    OracleCallableStatement st = (OracleCallableStatement) oc.prepareCall(
      "begin test.upper( :in_array, :out_array ); end;"
    );

    st.setARRAYAtName( "in_array", stringArray );
    st.registerOutParameter( "out_array", Types.ARRAY, "STRINGARRAY"); // Upper case again
    st.execute();

    String[] result = (String[])st.getARRAY( 2 ).getArray();

    System.out.println("Length: " + result.length);
    System.out.println("First: " + result[0]);
  } (catch SQLException e) {
    // Handle error
  }
}

If you must use a PL/SQL associative array as an input to your procedure then write a function that takes a collection and outputs an associative array of the appropriate type and then call that:

BEGIN TEST.UPPER( TO_ASSOC_ARRAY( :in_collection ), :out_array ); END;

Note: This answer is assuming using the Oracle driver in ojdbc6.jar which does not appear to have the OracleConnection.createOracleArray() method but it will, hopefully, be portable changing oracle.sql.ARRAY to java.sql.Array and using the newer methods.

I'm pretty sure we're using `ojdbc7.jar`. Will that matter? Would you change your answer? — dave, Dec 08 '17 at 20:34
The key tip - do not use associative arrays - helped me solve my issue. By simply changing the type of `out_array`, I was able to receive an array of strings back from the stored procedure. I did not need to change any Java client code. — dave, Dec 10 '17 at 22:52

How do I get a stored procedure output parameter that is an array to work?

1 Answers1

Linked