First create a type in the database that describes the collection that we want to open the ref cursor for:
create type num_tbl is table of number
Then create a function that will define and populate a nested table of this type, open a ref cursor for it and return that ‘handle’:
create or replace
function get_num_cursor
return sys_refcursor
is
l_num_tbl num_tbl;
l_idx integer;
l_refcursor sys_refcursor;
begin
l_num_tbl:= num_tbl(10,20);
l_num_tbl.extend(5);
l_num_tbl(l_num_tbl.last):= 30;
l_num_tbl.extend;
l_num_tbl(l_num_tbl.last):= 40;
open l_refcursor for select * from table(cast (l_num_tbl as num_tbl));
return l_refcursor;
end;
Source