How do I migrate an Oracle stored procedure into a function executed by a standard SELECT query?
I follow the steps below, but I keep getting the error and don't see the problem:
1) Create your own Oracle datatype that represents the database columns you want to retrieve:
CREATE TYPE my_object AS OBJECT
(COL1 VARCHAR2(50),
COL2 VARCHAR2(50),
COL3 VARCHAR2(50));
2) Create another datatype which is a table of the newly created object:
TYPE MY_OBJ_TABLE AS TABLE OF my_object;
3) Create a function that returns this table. Also use a pipeline clause to pipe the results back to the calling SQL, for example:
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 := my_object(null,null,null);
myObjClass VARCHAR2(50);
BEGIN
myObjClass := PXOBJCLASS
OPEN Cur1 For ‘select PYID, PXINSNAME, PZINSKEY from PC_WORK where PXOBJCLass = ;1’USING myObjClass,
LOOP
FETCH cur1 INTO out_rec.COL1, out_rec.COL2, out_rec.COL3;
EXIT WHEN Cur1%NOTFOUND;
PIPE ROW (out_rec);
END LOOP;
CLOSE Cur1;
RETURN;
END MY_FUNC;
NOTE. In the above example, you can easily 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 using the following SQL statement:
select COL1, COL2, COL3 from TABLE(MY_FUNC('SomeSampletask'));
a source to share
There is no need to use dynamic sql (dynamic sql is always a little slower) and there are too many variables declared. Also the for loop is much simpler. I renamed the function argument from pxobjclass to p_pxobjclass.
Try the following:
create or replace function my_func (p_pxobjclass in varchar2)
return my_obj_table pipelined
is
begin
for r_curl in (select pyid,pxinsname,pzinskey
from pc_work
where pxobjclass = p_pxobjclass) loop
pipe row (my_object(r_curl.pyid,r_curl.pxinsname,r_curl.pzinskey));
end loop;
return;
end;
EDIT1:
By the way, it's faster to return a ref cursor instead of a pipelined function that returns a nested table:
create or replace function my_func2 (p_pxobjclass in varchar2)
return sys_refcursor
is
l_sys_refcursor sys_refcursor;
begin
open l_sys_refcursor for
select pyid,pxinsname,pzinskey
from pc_work
where pxobjclass = p_pxobjclass;
return l_sys_refcursor;
end;
It's faster because it takes a while to create the objects (my_object).
a source to share
I see two problems:
-
Dynamic query doesn't work, try this:
'select PYID, PXINSNAME, PZINSKEY from PC_WORK, where PXOBJclass =' '' || PXOBJCLASS || '' ''
You don't need myObjClass and it seems like all your quotes are wrong.
-
Quoting in "SomeSampletask" ...
select COL1, COL2, COL3 from table (MY_FUNC ('SomeSampletask'));
a source to share