title: Fetch Oracle table type from stored procedure using JDBC
body: <p>I'm trying to understand different ways of getting table data from Oracle stored procedures / functions using JDBC. The six ways are the following ones:</p>
  
  <ol>
  <li>procedure returning a schema-level table type as an OUT parameter</li>
  <li>procedure returning a package-level table type as an OUT parameter</li>
  <li>procedure returning a package-level cursor type as an OUT parameter</li>
  <li>function returning a schema-level table type</li>
  <li>function returning a package-level table type</li>
  <li>function returning a package-level cursor type</li>
  </ol>
  
  <p>Here are some examples in PL/SQL:</p>
  
  
  
  <pre class="lang-sql prettyprint-override"><code>-- schema-level table type
  CREATE TYPE t_type AS OBJECT (val VARCHAR(4));
  CREATE TYPE t_table AS TABLE OF t_type;
  
  CREATE OR REPLACE PACKAGE t_package AS
    -- package level table type
    TYPE t_table IS TABLE OF some_table%rowtype;
    -- package level cursor type
    TYPE t_cursor IS REF CURSOR;
  END library_types;
  
  -- and example procedures:
  CREATE PROCEDURE p_1 (result OUT t_table);
  CREATE PROCEDURE p_2 (result OUT t_package.t_table);
  CREATE PROCEDURE p_3 (result OUT t_package.t_cursor);
  CREATE FUNCTION f_4 RETURN t_table;
  CREATE FUNCTION f_5 RETURN t_package.t_table;
  CREATE FUNCTION f_6 RETURN t_package.t_cursor;
  </code></pre>
  
  <p>I have succeeded in calling 3, 4, and 6 with JDBC:</p>
  
  
  
  <pre class="lang-java prettyprint-override"><code>// Not OK: p_1 and p_2
  CallableStatement call = connection.prepareCall("{ call p_1(?) }");
  call.registerOutParameter(1, OracleTypes.CURSOR);
  call.execute(); // Raises PLS-00306. Obviously CURSOR is the wrong type
  
  // OK: p_3
  CallableStatement call = connection.prepareCall("{ call p_3(?) }");
  call.registerOutParameter(1, OracleTypes.CURSOR);
  call.execute();
  ResultSet rs = (ResultSet) call.getObject(1); // Cursor results
  
  // OK: f_4
  PreparedStatement stmt = connection.prepareStatement("select * from table(f_4)");
  ResultSet rs = stmt.executeQuery();
  
  // Not OK: f_5
  PreparedStatement stmt = connection.prepareStatement("select * from table(f_5)");
  stmt.executeQuery(); // Raises ORA-00902: Invalid data type
  
  // OK: f_6
  CallableStatement call = connection.prepareCall("{ ? = call f_6 }");
  call.registerOutParameter(1, OracleTypes.CURSOR);
  call.execute();
  ResultSet rs = (ResultSet) call.getObject(1); // Cursor results
  </code></pre>
  
  <p>So obviously, I'm having trouble understanding</p>
  
  <ol>
  <li>How to retrieve schema-level and package-level table types from OUT parameters in stored procedures</li>
  <li>How to retrieve package-level table types from stored functions</li>
  </ol>
  
  <p>I can't seem to find any documentation on this, as everyone always uses cursors instead of table types. Maybe because it's not possible? I prefer table types, though, because they are formally defined and can be discovered using the dictionary views (at least the schema-level table types).</p>
  
  <p>Note: obviously, I could write a wrapper function returning the OUT parameters and package-level table types. But I'd prefer the clean solution.</p>