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
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
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
My problem is that i don't know %rowtype makes by outside query. — Arthur Romantsov
Then you need to a look at generic SQL types and anydataset specifically. Here is an example. — Nick Krasnov

Ashish sinha Ashish sinha · Accepted Answer · 2013-12-30T04:15:03

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 Answers