How to Use PIPE Function in Oracle (With an Example)

CREATE TABLE DEPT (DEPTNO NUMBER, DEPTNAME VARCHAR2(20))

create table emp
    (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)
   );
  
insert into emp values (7499, 'ALLEN', 'MANAGER', NULL, '20-FEB-81', 1000, NULL, 10);

insert into emp values (7500, 'SUDHAMSU', 'EMPLOYEE', NULL, '20-FEB-82', 2000, NULL, 20);

insert into emp values (7500, 'SRINI', 'EMPLOYEE', NULL, '20-FEB-83', 3000, NULL, 30);
----------------

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)
   )
   /

create or replace type emp_table_type as table of emp_scalar_type
/


 create or replace
    function emp_etl( p_cursor in sys_refcursor )
    return emp_table_type
    PIPELINED
    as
            l_rec  emp%rowtype;
            --l_rec  external_table%rowtype;
    begin
            loop
                    fetch p_cursor into l_rec;
                   exit when (p_cursor%notfound);

                   -- validation routine
                   -- log bad rows elsewhere
                   -- lookup some value
                   -- perform conversion

                   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 ) );

                   -- pipe row (l_rec); -- This would error as we should return the contents of the table and not the table itself.
           end loop;

           return;
   end;
   /
  
 select empno, ename
    from TABLE(emp_etl(cursor( select * from emp ) ) );