Oracle stored procedure call which returns PL/SQL TABLE data

, ,
1

I need to call an oracle stored procedure which returns a PL/SQL TABLE data type (OUT parameter). In the parameter tab of the adapter service, I selected “OTHER” as the JDBC type and “java.lang.Object” as the java type for this parameter. It displays an “Invalid Column type” exception when I run this adapter service.

How do I get Table type in my service. My requirement is to return an array of strings from Oracle stored procedure. I am using webMethods 6.0.1. Any help will be appreciated.

2

I do not know how to return a PL/SQL table but what we have done is to put the PL/SQL table data into a cursor and return the Oracle cursor from a stored procedure. There are a few steps that are required to get the information into the cursor:

create type record_type as object (
field1 varchar(30) );

create type table_type is table of record_type;

type ref_cursor is ref cursor;

table_table table_type;

–fill table

–8.0.6 syntax
–(I do not know if this will also work in 8.1.7)
open ref_cursor for
select
field1
from the(select
(cast (table_table as table_type))
from dual);

–8.1.7 syntax (I believe)
open ref_cursor for
select
field1
from table(cast(table_table as table_type));