Oracle PLSQL Demo

--PACKAGE
CREATE OR REPLACE PACKAGE test_141213 is
    TYPE type_ref IS record(
        ENAME VARCHAR2(20),
        WORK_CITY 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_141213;


-- PACKAGE BODY
CREATE OR REPLACE PACKAGE BODY test_141213 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 e.ename, d.loc as work_city, e.sal from scott.emp e, scott.dept d where e.deptno = d.deptno';
    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.work_city || '   ' || cur_type_ref.sal);
            PIPE ROW(cur_type_ref);
        End Loop;
        Close cur_variable;
    
        RETURN;
    END;
END test_141213;

--Test retrieve
select * from table(test_141213.retrieve('Robin'));