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