Oracle Pipelined Function

The following sample demonstrates Pipelined function in Oracle.

exec dbms_output.enable(1000000);

SET SERVEROUTPUT ON

      CREATE OR REPLACE
      TYPE table_rec FORCE AS OBJECT (
           person_code number(10)
          ,visa_type varchar2(40)
          ,prm number(10)
      );
/                  

      CREATE OR REPLACE
      TYPE my_table FORCE AS table of table_rec;
/

      CREATE OR REPLACE FUNCTION get_table_rows(
             p_person_code IN NUMBER
      ) RETURN my_table PIPELINED AS
               v_my_table my_table;
            BEGIN
                  SELECT table_rec(TBL.person_code
                                  ,TBL.visa_type
                                  ,TBL.p_person_code
                  )
                  BULK COLLECT INTO v_my_table
                  FROM
                  (SELECT person_code
                          ,visa_type
                          ,p_person_code
                  FROM visas
                  where person_code = p_person_code
                  ) TBL;

                FOR i IN v_my_table.FIRST .. v_my_table.LAST LOOP
                  PIPE ROW(v_my_table(i));
                END LOOP;
                RETURN;
            EXCEPTION  
                WHEN OTHERS THEN  
                    DBMS_OUTPUT.PUT_LINE(SQLCODE || ' ' || SQLERRM);
      END get_table_rows;
/

 The script below runs this function:

DECLARE
      CURSOR C IS select person_code
                         ,registration_no
                         ,forename
                         ,surname 
                    from table(get_table_rows(182));
   l_rec C%ROWTYPE;
   l_start date;
   l_finish date;
BEGIN
    l_start := sysdate;
     OPEN C;
     LOOP
        FETCH C INTO l_rec;
        DBMS_OUTPUT.PUT_LINE(l_rec.person_code);
        EXIT WHEN C%NOTFOUND;
     END LOOP;
     CLOSE C;
     l_finish := sysdate;
     dbms_output.put_line('Time difference = ' || (l_finish - l_start) * (24 * 60 * 60));
END;

If you find this script useful please donate generously.