-- object type declaration (schema level)
create or replace type t_emp_ob is object (
       empno_ob number(4),
       ename_ob varchar2(10)
);
--------------
-- nested table declaration (schema level)
create or replace type t_emp_nt is table of t_emp_ob;
--------------
-- ref cursor declaration (package level)
create or replace package refcur_ptf_pkg
is
       type t_emp_rt is record (empno_ob number(4), ename_ob varchar2(10));
       type refcur_ptf is ref cursor return t_emp_rt;
end refcur_ptf_pkg;

create or replace function ptf (dataset in refcur_ptf_pkg.refcur_ptf) 
return t_emp_nt pipelined 
is
   type dataset_tt is table of refcur_ptf_pkg.t_emp_rt index by pls_integer;
   l_dataset dataset_tt;
begin
  fetch dataset bulk collect into l_dataset;
  
  for l_row in l_dataset.first .. l_dataset.last loop
    pipe row(t_emp_ob(l_dataset(l_row).empno_ob, l_dataset(l_row).ename_ob));
    dbms_lock.sleep(1);
  end loop;
  
  close dataset;

end;

select *
from table(ptf(cursor(select empno, ename from regrep.emp)));

-- object type declaration (schema level)
create or replace type t_emp_ob is object (
       empno_ob number(4),
       ename_ob varchar2(10)
);
--------------
-- nested table declaration (schema level)
create or replace type t_emp_nt is table of t_emp_ob;
--------------
-- ref cursor declaration (package level)
create or replace package refcur_ptf_pkg
is
       type refcur_ptf is ref cursor;
end refcur_ptf_pkg;

create or replace function tf_doesnt_work (dataset in refcur_ptf_pkg.refcur_ptf) 
return t_emp_nt 
is
   type dataset_tt is table of t_emp_ob index by pls_integer;
   l_dataset dataset_tt;
   retval t_emp_nt := t_emp_nt();
begin
  fetch dataset bulk collect into l_dataset;
  
  for l_row in l_dataset.first .. l_dataset.last loop
    retval.extend;
    retval(retval.last) := t_emp_ob(l_dataset(l_row).empno_ob, l_dataset(l_row).ename_ob);
  end loop;
  
  close dataset;
  return retval;
end;

-- Doesn't work
select *
from table(tf_doesnt_work(cursor(select empno, ename from regrep.emp)));

set arraysize 1

(dataset in refcur_ptf_pkg.refcur_ptf)
изложите в виде 
(dataset in SYS_REFCURSOR)

fetch dataset bulk collect into l_dataset;

-- object type declaration (schema level)
create or replace type t_emp_ob is object (
       empno_ob number(4),
       ename_ob varchar2(10)
);

--------------
-- nested table declaration (schema level)
create or replace type t_emp_nt is table of t_emp_ob;
--------------
-- ref cursor declaration (package level)
create or replace package refcur_ptf_pkg
is
       type refcur_ptf is ref cursor;
end refcur_ptf_pkg;
-------------- ******************************


create or replace function tf_doesnt_work (dataset in SYS_REFCURSOR/*refcur_ptf_pkg.refcur_ptf*/) 
return t_emp_nt 
is
   type dataset_tt is table of t_emp_ob index by pls_integer;
   l_dataset dataset_tt;
   retval t_emp_nt := t_emp_nt();
begin
  fetch dataset bulk collect into l_dataset;
  
  for l_row in l_dataset.first .. l_dataset.last loop
    retval.extend;
    retval(retval.last) := t_emp_ob(l_dataset(l_row).empno_ob, l_dataset(l_row).ename_ob);
  end loop;
  
  close dataset;
  return retval;
end;

-- Doesn't work
select *
from table(tf_doesnt_work(cursor(select empno, ename from regrep.emp)));