CREATE OR REPLACE TYPE myemp AS OBJECT ( empno number, ename varchar2(10), job varchar2(10), mgr number, hiredate date, sal number, comm number, deptno number ); CREATE OR REPLACE TYPE myrectable AS TABLE OF myemp ; enter code hereCREATE OR REPLACE FUNCTION pipedata(p_min_row number, p_max_row number) RETURN myrectable PIPELINED IS v_obj myemp := myemp(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL); BEGIN FOR e IN (select * from ( select e.* ,rownum rn from (select * from emp order by empno) e ) where rn between p_min_row and p_max_row) LOOP v_obj.empno := e.empno; v_obj.ename := e.ename; v_obj.job := e.job; v_obj.mgr := e.mgr; v_obj.hiredate := e.hiredate; v_obj.sal := e.sal; v_obj.comm := e.comm; v_obj.deptno := e.deptno; PIPE ROW (v_obj); END LOOP; RETURN; END;SQL> select * from table(pipedata(1,5));
Open in new window
shareimprove this answer