Skip to main content


         This documentation site is for previous versions. Visit our new documentation site for current releases.      
 

This content has been archived and is no longer being updated.

Links may not function; however, this content may be relevant to outdated versions of the product.

How to process a result set that is returned from Oracle stored procedures

Updated on October 19, 2020

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.

Have a question? Get answers now.

Visit the Support Center to ask questions, engage in discussions, share ideas, and help others.

Did you find this content helpful?

Want to help us improve this content?

We'd prefer it if you saw us at our best.

Pega.com is not optimized for Internet Explorer. For the optimal experience, please use:

Close Deprecation Notice
Contact us