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.

Calling a stored procedure that returns a resultset (Oracle)

Updated on October 19, 2020

Your application can use SQL queries to retrieve data to use in rules processing or to populate lists in use forms. SQL queries typically return resultsets with tabular data.

Oracle stored procedures cannot return a resultset object. Rather, Oracle uses a ref cursor object to return data to the client from a stored procedure. This limits the use of Oracle stored procedures to return database result-set objects to your application.

You can work around this limitation by wrapping an Oracle stored procedure in a function that is executed by a standard SELECT query, allowing a resultset to be returned.

Suggested approach

To wrap an Oracle stored procedure in a function, do the following:

  1. Create a custom Oracle datatype that represents the database columns that you want to retrieve:
    (COL1 VARCHAR2(50),
    COL2 VARCHAR2(50),
    COL3 VARCHAR2(50));
  2. Create another datatype that is a table of the object that you just created:


  3. Create a function that returns this table. Also, use a pipeline clause so that results are pipelined back to the calling SQL, for example:

    create or replace type my_object as object
    (PYID        varchar2(50),
     PXINSNAME   varchar2(50),
     PZINSKEY    varchar2(50));

    create or replace type my_obj_table as table of my_object;

    create or replace function my_func
        (PXOBJCLASS varchar2)
    return my_obj_table
        type ref1 is ref cursor;
        cur1 ref1;
        out_rec my_object;
        myObjClass varchar2(50);
        myObjClass := PXOBJCLASS;
        out_rec := my_object(null, null, null);
        open cur1 for 'select PYID, PXINSNAME, PZINSKEY from PC_WORK where PXOBJCLass = :1'
           using myObjClass;
            fetch cur1 into out_rec.PYID, out_rec.PXINSNAME, out_rec.PZINSKEY;
            exit when cur1%notfound;
            pipe row (out_rec);
        end loop;
        close cur1;


    end my_func;

    In the preceding example, you can replace the select statement with a call to another stored procedure that returns a cursor variable.
  4. In your application, call this function as a table function by using the following SQL statement in Rule-Declare-SQL:

    select * from table(my_func('PegaSample'));

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. is not optimized for Internet Explorer. For the optimal experience, please use:

Close Deprecation Notice
Contact us