pl/sql table function with weak ref cursor

Question

I need to return from table function string like inside ref cursor;

create or replace 
          FUNCTION get_data(QUERY in VARCHAR2)
RETURN [SOMETHING] pipelined
    is
    ret sys_refcursor;
BEGIN
    open ret for QUERY;
    Loop
    fetch ret into [SOMETHING];
    exit when ret%notfound;
    pipe row(str);
    end loop;
    close ret;
END get_data;

Any idea, how I can return type like ret%rowtype.

User can call this like select * from table(get_data(any select from any table)); — Arthur Romantsov, Dec 01 '13 at 16:38
That's not what I was asking, but it's OK. Why the user cannot simply execute `select .. from any_table`, without invoking the `get_data()` function, which according to your example does nothing but returns result of a query without transformation. Why would you even need that function? — Nick Krasnov, Dec 01 '13 at 16:45
Are you sure you got the requirements correctly? Pipelined table function has to return a collection, elements of which are of scalar or composite data type. So in order to make that function work you 1) need to declare a collection either as schema level object or in a package specification; 2) In order to successfully declare that collection you need to know the type your ref_cursor (`ret`) returns. So, it basically rules out the possibility to pass `select .. from any_table` in to that function. — Nick Krasnov, Dec 01 '13 at 17:25
My problem is that i don't know %rowtype makes by outside query. — Arthur Romantsov, Dec 01 '13 at 17:41
Then you need to a look at generic SQL types and `anydataset` specifically. [Here is an example](http://stackoverflow.com/questions/14155844/return-resultset-from-function). — Nick Krasnov, Dec 01 '13 at 18:00
possible duplicate of [Return N columns from a table function](http://stackoverflow.com/questions/20281578/return-n-columns-from-a-table-function) — Alen Oblak, Dec 01 '13 at 19:39

score 0 · Answer 1 · answered Dec 30 '13 at 04:15

declare

type auth_cursor is ref cursor

return employees%rowtype;

c2 auth_cursor;

r_c2 c2%rowtype;

function get_auth return auth_cursor

is

c1 auth_cursor;

begin

open c1 for select * from employees;

return c1;

end;

begin

c2 := get_auth;

loop

fetch c2 into r_c2;

exit when c2%notfound;

dbms_output.put_line(initcap(

r_c2.last_name));

end loop;

close c2;

end;

pl/sql table function with weak ref cursor

1 Answers1