title: How do I get a stored procedure output parameter that is an array to work?
body: <p>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.</p>
  
  <p>Here's the PL/SQL code I have:</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>Here's the Java code I have:</p>
  
  <pre><code>public void testString(Connection connection) {
      String[] values = { "alpha", "beta", "gamma" };
      try {
         CallableStatement callableStatement = connection.prepareCall("begin test.upper(?, ?); end;");
         DelegatingConnection&lt;OracleConnection&gt; delegatingConnection = (DelegatingConnection&lt;OracleConnection&gt;) 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
      }
  }
  </code></pre>
  
  <p>If I call the PL/SQL stored procedure from an SQL script directly it works. So I think the stored procedure itself is okay.</p>
  
  <p>If I call the stored procedure via JDBC it completes normally. Using debug statements, I have confirmed that <code>values</code> is correctly sent from the Java client to the stored procedure into <code>in_array</code>. That is, an array of length 3 with the appropriate values is received. As best as I can tell, <code>out_array</code> is sent back to the Java client. However, something goes wrong. <code>result</code> is of size 3 but all the elements are <code>null</code>.</p>
  
  <p>If I inspect <code>output</code>, I can see that internally it has a <code>byte[]</code> 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 <code>String[]</code> correctly.</p>
  
  <p>What am I doing wrong?</p>