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.
To wrap an Oracle stored procedure in a function, do the following:
- Create a custom Oracle datatype that represents the database columns that you want to retrieve:
CREATE TYPE my_object AS OBJECT
- Create another datatype that is a table of the object that you just created:
TYPE MY_OBJ_TABLE AS TABLE OF my_object;
- 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
create or replace type my_obj_table as table of my_object;
create or replace function my_func
type ref1 is ref cursor;
myObjClass := PXOBJCLASS;
out_rec := my_object(null, null, null);
open cur1 for 'select PYID, PXINSNAME, PZINSKEY from PC_WORK where PXOBJCLass = :1'
fetch cur1 into out_rec.PYID, out_rec.PXINSNAME, out_rec.PZINSKEY;
exit when cur1%notfound;
pipe row (out_rec);
/In the preceding example, you can replace the select statement with a call to another stored procedure that returns a cursor variable.
- 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'));