Then you can query the function as if it were a table
-- type to match emp record create or replace type emp_scalar_type as object (EMPNO NUMBER(4) , ENAME VARCHAR2(10), JOB VARCHAR2(9), MGR NUMBER(4), HIREDATE DATE, SAL NUMBER(7, 2), COMM NUMBER(7, 2), DEPTNO NUMBER(2) ) / -- table of emp records create or replace type emp_table_type as table of emp_scalar_type / -- pipelined function create or replace function get_emp( p_deptno in number ) return emp_table_type PIPELINED as TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE; emp_cv EmpCurTyp; l_rec emp%rowtype; begin open emp_cv for select * from emp where deptno = p_deptno; loop fetch emp_cv into l_rec; exit when (emp_cv%notfound); pipe row( emp_scalar_type( l_rec.empno, LOWER(l_rec.ename), l_rec.job, l_rec.mgr, l_rec.hiredate, l_rec.sal, l_rec.comm, l_rec.deptno ) ); end loop; return; end; /
Is that what you were looking for?
select * from table(get_emp(20))
How could I make the PL block behave the same way as the SQL query, i.e. present its output in a query result window?Check Tom's 3rd suggestion to use a plsql table type and a select: