create or replace package dp_nest_test is function get_lx3_types return t_nest_type; function ret_lx3_point(r sys_refcursor) return t_nest_type; function ret_pipe return t_nest_type_r pipelined; end; / create or replace package body dp_nest_test is function get_lx3_types return t_nest_type is v_nest_type t_nest_type:=t_nest_type(''); begin select type bulk collect into v_nest_type from lx3; return v_nest_type; end; function ret_lx3_point(r sys_refcursor) return t_nest_type is v_nest_tab t_nest_type:=t_nest_type(); k int :=1; t varchar2(200); begin loop fetch r into t; if r%notfound then exit; end if; v_nest_tab.extend; v_nest_tab(k):=t||'('||ascii(t)||')'; k:=k+1; end loop; return v_nest_tab; end; function ret_pipe return t_nest_type_r pipelined is v_lx3_r r_lx3:=r_lx3('',''); begin for i in(select xh,type from lx3) loop v_lx3_r.xh:=i.xh; v_lx3_r.type:=i.type; --pipe row(v_lx3_r); pipe row(r_lx3(i.xh,i.type)); end loop; return; end;
end; /
select * from table(cast(dp_nest_test.get_lx3_types as t_nest_type)); select * from table(dp_nest_test.ret_lx3_point(cursor (select type as t_nest_type from lx3))); select * from table(dp_nest_test.ret_pipe);
create or replace package dp_nest_test is function get_lx3_types return t_nest_type; function ret_lx3_point(r sys_refcursor) return t_nest_type; function ret_pipe return t_nest_type_