--PACKAGE CREATE OR REPLACE PACKAGE test_141215 is TYPE type_ref IS record( ENAME VARCHAR2(20), SAL NUMBER(10)); TYPE t_type_ref IS TABLE OF type_ref; FUNCTION retrieve(v_name varchar2) RETURN t_type_ref PIPELINED; END test_141215; -- PACKAGE BODY CREATE OR REPLACE PACKAGE BODY test_141215 IS FUNCTION retrieve(v_name varchar2) RETURN t_type_ref PIPELINED IS cur_type_ref type_ref; Type ref_cur_variable IS REF cursor; cur_variable ref_cur_variable; --rec_emp type_ref%RowType; v_sql varchar2(100) := 'select t.ename, t.sal/*, t.empno*/ from scott.emp t'; BEGIN Open cur_variable For v_sql; Loop fetch cur_variable InTo cur_type_ref; Exit When cur_variable%NotFound; dbms_output.put_line(cur_variable%rowcount || ' -> ' || cur_type_ref.ename || ' ' || cur_type_ref.sal); PIPE ROW(cur_type_ref); End Loop; Close cur_variable; RETURN; END; END test_141215; --Test retrieve select * from table(test_141215.retrieve('asd'));