How to call a procedure with out parameter as a table type from a Java class

Question

I want to call this procedure get_data_Q1 in the ult_pkg package from Java code and display the output:

 CREATE OR REPLACE PACKAGE ult_pkg AS TYPE t_all_record is record ( x_object_type_id number, x_object_name varchar2(100), x_object_id varchar2(70), x_audit_timestamp timestamp(6), x_payload clob ); --table type to hold table data after querying type tt_all_tab is table of t_all_record index by binary_integer; --declaration pocedures procedure get_data_Q1(x_object_id in varchar2 , x_all_type out tt_all_tab ); end ult_pkg; / --body of 'ult_pkg' package create or replace package body ult_pkg AS --procedure taking 'object_id' as input parameter and gives out table of 't_all_record' type procedure get_data_Q1(x_object_id in varchar2 , x_all_type out tt_all_tab ) AS i number:=0; begin for r in ( SELECT O.object_type_id,O.object_name,O.object_id,A.audit_timestamp,P.payload FROM APPLICATION APP, EXCEPTIONS E,MASTER_AUDIT A,MODULE_TYPE M,OBJECT_TYPE O,PAYLOAD P WHERE ( A.MODULE_TYPE_ID = M.MODULE_TYPE_ID ) AND ( M.APPLICATION_ID = APP.APPLICATION_ID ) AND ( A.OBJECT_TYPE_ID = O.OBJECT_TYPE_ID ) AND ( O.OBJECT_ID = x_object_id ) ) -- loop to asign the data from cursor 'r' to carasponding table type columns loop x_all_type(i).x_object_type_id:=r.object_type_id; x_all_type(i).x_object_name:=r.object_name; x_all_type(i).x_object_id:=r.object_id; x_all_type(i).x_audit_timestamp:=r.audit_timestamp; x_all_type(i).x_payload:=r.payload; i:=i+1; end loop; end get_data_Q1; end ult_pkg; / 

Answer 1

This is not possible, see Accessing PL / SQL Index Tables :

Oracle JDBC does not support RAW, DATE, and PL / SQL RECORD as item types.

I would probably use a custom (global, not batch) object type, for example:

 CREATE TYPE t_all_record AS OBJECT ( x_object_type_id number, x_object_name varchar2(100), x_object_id varchar2(70), x_audit_timestamp timestamp(6), x_payload clob ) / CREATE TYPE t_all_records IS TABLE OF t_all_record / 

specify the type table in your package (t_all_records instead of tt_all_tab) and fill it like this:

 procedure get_data_Q1(x_object_id in varchar2 , x_all_type out t_all_records ) AS begin SELECT t_all_record(O.object_type_id,O.object_name,O.object_id,A.audit_timestamp,P.payload) BULK COLLECT INTO x_all_type FROM APPLICATION APP, EXCEPTIONS E,MASTER_AUDIT A,MODULE_TYPE M,OBJECT_TYPE O,PAYLOAD P WHERE ( A.MODULE_TYPE_ID = M.MODULE_TYPE_ID ) AND ( M.APPLICATION_ID = APP.APPLICATION_ID ) AND ( A.OBJECT_TYPE_ID = O.OBJECT_TYPE_ID ) AND ( O.OBJECT_ID = x_object_id ) end get_data_Q1; 

The result will be used from java as follows:

 package tests.jdbc; import java.sql.Array; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSetMetaData; import java.sql.Struct; import java.sql.Types; import oracle.sql.StructDescriptor; public class OracleTableOfResult { public static void main(String...a) throws Exception { Class.forName("oracle.jdbc.OracleDriver"); Connection connection = DriverManager.getConnection("jdbc:oracle:thin:<USER>/<PASS>@<DATABASEHOST>:1521:<SERVICE>"); final String typeName = "T_ALL_RECORD"; final String typeTableName = "T_ALL_RECORDS"; // Get a description of your type (Oracle specific) final StructDescriptor structDescriptor = StructDescriptor.createDescriptor(typeName.toUpperCase(), connection); final ResultSetMetaData metaData = structDescriptor.getMetaData(); // Call the procedure (or whatever else) that returns the table of a custom type CallableStatement cs = connection.prepareCall("{call ult_pkg.get_data_Q1(?, ?)}"); cs.setString(1, "the_id"); // Result is an java.sql.Array... cs.registerOutParameter(2, Types.ARRAY, typeTableName); cs.execute(); // ...who elements are java.sql.Structs Object[] data = (Object[]) ((Array) cs.getObject(2)).getArray(); for(Object tmp : data) { Struct row = (Struct) tmp; // Attributes are index 1 based... int idx = 1; for(Object attribute : row.getAttributes()) { System.out.println(metaData.getColumnName(idx) + " = " + attribute); ++idx; } System.out.println("---"); } cs.close(); connection.close(); } } 

But in the end, it is doubtful whether it's worth it when you can use your query in a simple sql expression as a prepared statement ...