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;
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