Can I return a table of custom object as an OUT parameter when calling stored procedure in JDBC

Question

In Oracle 11g I have a stored procedure like this:

PROCEDURE test_params (o_message_tbl OUT core.message_tbl);

where

create or replace TYPE message_tbl AS TABLE OF core.message_obj;
create or replace TYPE message_obj AS OBJECT
              (code NUMBER (10, 0),
               type_code VARCHAR2 (10 CHAR),
               text VARCHAR2 (1000 CHAR)
              )

I am using Spring's SimpleJdbcCall to call it, but it gives me "Invalid column type" exception. Is it possible to call such procedure and to read result in JDBC? If no, what other options are available except returning a cursor?

Are you using a CallableStatement? – Pablo Santa Cruz Jan 24 '11 at 13:08 — Pablo Santa Cruz, Jan 24 '11 at 13:08

score 2 · Accepted Answer · answered Jan 24 '11 at 13:45

Spring and SimpleJdbcCall don't do anything fancy, they just make JDBC a bit easier to use.

There's an example of how to use TABLE return types in JDBC here: http://www.velocityreviews.com/forums/t138431-access-oracle-objects-via-plsql-from-jdbc.html

You should be able to adapt that to SimpleJdbcCall fairly easily.

Can I return a table of custom object as an OUT parameter when calling stored procedure in JDBC

1 Answers1