title: Oracle JDBC call PL/SQL procedure with input parameter a table of records
body: <p>I am trying to call the following pl/sql procedure from JDBC.</p>
  
  <pre><code>create or replace PACKAGE test AS
  
  type testrec_r is record (
   val1 number,
   val2 varchar2(100)
   );
  
   type testarr_t is table of testrec_r index by binary_integer;
  
  function test_func(i_data in testarr_t, o_sum out number, o_totallength out     number) return number;
  
  END test;
  </code></pre>
  
  <p>This is how I tried to invoke it, but without success:</p>
  
  <pre><code>StructDescriptor recDescriptor = StructDescriptor.createDescriptor("test.testrec_r", conn);
      STRUCT[] RECORDS_ARRAY = new STRUCT[2];
  
      for (int i = 0; i &lt; 2; i++) {
           STRUCT oracle_record = new STRUCT(recDescriptor, conn, new
           Object[] {i, "test"});
           RECORDS_ARRAY[i] = oracle_record;
      }
  
      CallableStatement stmt = conn.prepareCall("{ call TEST.TEST_FUNC(?, ?, ?) }");
  
      ArrayDescriptor arrayDescriptor = ArrayDescriptor.createDescriptor("TEST.TESTARR_T", conn);
      ARRAY oracle_array = new ARRAY(arrayDescriptor, conn, RECORDS_ARRAY);
  
      // Bind the input record
      stmt.setArray(1, oracle_array);
      stmt.registerOutParameter(2, Types.NUMERIC);
      stmt.registerOutParameter(3, Types.NUMERIC);
      stmt.executeUpdate();
      double out1 = stmt.getDouble(2);
      double out2 = stmt.getDouble(3);
      return new Object[] { out1, out2 };
  </code></pre>
  
  <p>I just have read that oracle jdbc does not support pl/sql struct types. So, this fails with "invalid name pattern: test.testrec_r"</p>
  
  <p>How can I call this procedure from Java? Ideally would be to only use a java libray/API, but as this seems almost imposible, which is the best workaround to wrap the pl/sql package in simple sql call and to invoke it?</p>
  
  <p>P.S I am using Spring JDBCTemplate for database connection.</p>