Skip to main content
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.
LinkedIn
Copied!

Calling a stored procedure that returns a resultset (Oracle)

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:
    CREATE TYPE my_object AS OBJECT
    (COL1 VARCHAR2(50),
    COL2 VARCHAR2(50),
    COL3 VARCHAR2(50));
  2. Create another datatype that is a table of the object that you just created:

    TYPE MY_OBJ_TABLE AS TABLE OF my_object;

  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
    pipelined
    is
        type ref1 is ref cursor;
        cur1 ref1;
        out_rec my_object;
        myObjClass varchar2(50);
    BEGIN
        myObjClass := PXOBJCLASS;
        out_rec := my_object(null, null, null);
        open cur1 for 'select PYID, PXINSNAME, PZINSKEY from PC_WORK where PXOBJCLass = :1'
           using myObjClass;
        loop
            fetch cur1 into out_rec.PYID, out_rec.PXINSNAME, out_rec.PZINSKEY;
            exit when cur1%notfound;
            
            pipe row (out_rec);
        end loop;
        
        close cur1;

        return;

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

Did you find this content helpful?

75% found this useful

Have a question? Get answers now.

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

Ready to crush complexity?

Experience the benefits of Pega Community when you log in.

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

Pega Community has detected you are using a browser which may prevent you from experiencing the site as intended. To improve your experience, please update your browser.

Close Deprecation Notice
Contact us