Fetch Oracle table type from stored procedure using JDBC

Viewed 33121

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:

  1. procedure returning a schema-level table type as an OUT parameter
  2. procedure returning a package-level table type as an OUT parameter
  3. procedure returning a package-level cursor type as an OUT parameter
  4. function returning a schema-level table type
  5. function returning a package-level table type
  6. function returning a package-level cursor type

Here are some examples in PL/SQL:

-- 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;

I have succeeded in calling 3, 4, and 6 with JDBC:

// 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

So obviously, I'm having trouble understanding

  1. How to retrieve schema-level and package-level table types from OUT parameters in stored procedures
  2. How to retrieve package-level table types from stored functions

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).

Note: obviously, I could write a wrapper function returning the OUT parameters and package-level table types. But I'd prefer the clean solution.

2 Answers
Related