Edit Question - KnowledgeHub

Title Body

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: procedure returning a schema-level table type as an OUT parameter procedure returning a package-level table type as an OUT parameter procedure returning a package-level cursor type as an OUT parameter function returning a schema-level table type function returning a package-level table type 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 = co

Tags (comma-separated) Cancel