Invoking a stored procedure in the Database Inbound protocol’s Read Action

There can be one stored procedure in the inbound Read Action. The stored procedure that is used in the Database Inbound Read Action must return a result set. If no result set is returned, then there is no message generated from the Database Inbound protocol.

No place holder is permitted in the inbound Read Action.

For the Oracle database, to return the resultset you must use the OUT CURSOR flag to define an OUT CURSOR parameter.

Example:

{call sptest_select(rowset OUT CURSOR)}

The rowset parameter is an OUT parameter that represents a CURSOR. The engine retrieves the result set from the OUT parameter rowset.

The definition of the sptest_select stored procedure is:

create or replace procedure sp_dbp_select(out_var out sys_refcursor)
IS
BEGIN
open out_var for select * from TABLE_SANITY order by TABLE_SANITY.ID;
END; 
Note:  OUT CURSOR is only used for the Oracle database. Multiple CURSORs are also supported.

Oracle example

{call sp_dbp_read_multiple( out_var1 OUT CURSOR, out_var2 OUT CURSOR)}

The out_var1 and out_var2 are OUT parameters that represent two CURSOR elements. The engine retrieves the result sets from these two OUT parameters.

The definition of the sp_dbp_read_multiple stored procedure is:

create or replace procedure sp_dbp_read_multiple(out_var1 out sys_refcursor, out_var2 out sys_refcursor)
IS
BEGIN
open out_var1 for select * from TABLE_SANITY where TABLE_SANITY.FLAG=0 order by TABLE_SANITY.ID;
open out_var2 for select * from TABLE_SANITY where TABLE_SANITY.FLAG=1 order by TABLE_SANITY.ID;
END sp_dbp_read_multiple;

Example of other databases:

{?=call sp_dbp_read_multiple()}

The definition of this sp_dbp_read_multiple stored procedure is:

create procedure sp_dbp_read_multiple
AS
BEGIN
select * from Table_sanity where Table_sanity.flag=0 order by Table_sanity.ID
select * from Table_sanity where Table_sanity.flag=1 order by Table_sanity.ID
END