Your application can use SQL queries in Connect-SQL rules to retrieve data to use in rules processing or to populate lists in use forms. SQL queries typically return result sets with tabular data to Process Commander (PRPC).

For PRPC 5.5 and later, this article describes how to work with result set output parameters (SYS_REFCURSOR) that are returned from Oracle stored procedures by using the resultset keyword.

Suggested approach

To create and call a stored procedure that returns a result set:

1. Create a stored function that will return at least one result set or contains result set inputs or outputs:

   CREATE OR REPLACE FUNCTION browseFunc (
           inkey1 in varchar,
           inkey2 in varchar,
           inkey3 in varchar,
           out1 out varchar,
           out2 out varchar,
           aResult1 out SYS_REFCURSOR,
           aResult2 out SYS_REFCURSOR)
           return SYS_REFCURSOR
         AS
           return_results SYS_REFCURSOR;
         begin
           out1 := 'out1';
           out2 := 'out2';
           open aResult1 for select name,rank from  tableName  where name like inkey1 || '%';
           open aResult2 for select name,rank from  tableName  where name like inkey2 || '%';
           open return_results for select name,rank from  tableName  where name like inkey3 || '%';
           return return_results;
        end;

   The function above returns a result set and also has two output parameters that are defined as a result set.

2. Enter the following RDB SQL to call the created function. To call this function from PRPC, enter the following into the Browse tab of the Connect-SQL rule:

   \{{resultset} = call browseFunc(
           {dataPage.inkey1},
           {dataPage.inkey2},
           {dataPage.inkey3},
           {dataPage.OutProperty1 out},
           {dataPage.OutProperty2 out},
           {resultset},
           {resultset})\}

3. The {resultset} keyword instructs the Process Commander engine to expect a result set on the specified parameter.