CREATE OR REPLACE TYPE emp_type
AS OBJECT (
EMPNO NUMBER(4,0),
ENAME VARCHAR2(10 BYTE),
JOB VARCHAR2(9 BYTE),
HIREDATE DATE,
SAL NUMBER(7,2),
DEPTNO NUMBER(2,0)); /
CREATE OR REPLACE TYPE emp_tab_type
AS TABLE OF emp_type;
CREATE OR REPLACE FUNCTION get_emp_tab
RETURN emp_tab_type
PIPELINED IS
BEGIN FOR rec IN (SELECT * FROM emp) LOOP
PIPE ROW (emp_type( rec.empno,
rec.ename, rec.job, rec.hiredate,
rec.sal, rec.deptno)) ;
END LOOP; END;
/
SELECT * FROM table(get_emp_tab);