How to wrap an Oracle stored procedure in a function that gets executed by a standard SELECT query?

Question

I am following these steps, but I continue to get an error and don't see the issue:

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 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
    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 example above, 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'));

Answer 1

I see two problems:

1. The dynamic query does not work that way, try this:

   'select PYID, PXINSNAME, PZINSKEY from PC_WORK where PXOBJCLass ='''||PXOBJCLASS||''''

You don't need myObjClass, and it seems all your quotes are wrong.

1. The quoting on 'SomeSampletask'...

   select COL1, COL2, COL3 from TABLE(MY_FUNC('SomeSampletask'));

Answer 2

Maybe I'm misunderstanding something here, but it seems like you want to be using a VIEW.

Answer 3

There is no need to use dynamic sql (dynamic sql is always a little bit slower) and there are too many variables declared. Also the for loop is much easier. I renamed the argument of the function from pxobjclass to p_pxobjclass.

Try this:

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:

It is by the way 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;

This is faster because creating objects (my_object) takes some time.