Passing data from one table function to another in a pipelined fashion : Cursor function « Cursor « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Cursor
3. Cursor function

SQL>
SQL> CREATE OR REPLACE PACKAGE pkg_table_func
  2  IS
  3    TYPE address_rec IS RECORD
  4    (LINE1 VARCHAR2(20),
  5     LINE2 VARCHAR2(20),
  6     CITY VARCHAR2(20),
  7     STATE_CODE VARCHAR2(2),
  8     ZIP VARCHAR2(13),
  9     COUNTRY_CODE VARCHAR2(4));
 10
 11     TYPE temp_adds IS TABLE OF address_rec;
 12  END;
 13  /

Package created.

SQL>
SQL> CREATE OR REPLACE FUNCTION myProc
  2  RETURN pkg_table_func.temp_adds
  3  PIPELINED
  4  IS
  5    addressValue pkg_table_func.address_rec;
  6  BEGIN
  7    FOR i IN 1..3 LOOP
  8      IF (i=1) THEN
  9        addressValue.line1 :='20 Spring St.';
 10        addressValue.line2 :=null;
 11        addressValue.city :='New York';
 12        addressValue.state_code :='NY';
 13        addressValue.zip :='10020';
 14        addressValue.country_code :='USA';
 15      ELSIF (i=2) THEN
 16        addressValue.line1 :='Suite 206';
 17        addressValue.line2 :='P Blvd';
 18        addressValue.city :='B';
 19        addressValue.state_code :='IL';
 20        addressValue.zip :='60000';
 21        addressValue.country_code :='USA';
 22      ELSIF (i=3) THEN
 23        addressValue.line1 :='1  Dr.';
 24        addressValue.line2 :=null;
 25        addressValue.city :='Vancouver';
 26        addressValue.state_code :='NJ';
 27        addressValue.zip :='22222';
 28        addressValue.country_code :='USA';
 29      END IF;
 30      PIPE ROW(addressValue);
 31    END LOOP;
 32    RETURN;
 33  END;
 34  /

Function created.

SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE FUNCTION f_table_plsql2_pipelined(p_ref_cursor SYS_REFCURSOR)
  2  RETURN pkg_table_func.temp_adds PIPELINED
  3  IS
  4    addressValue1 pkg_table_func.address_rec;
  5    addressValue2 pkg_table_func.address_rec;
  6  BEGIN
  7    LOOP
  8      FETCH p_ref_cursor INTO addressValue1;
  9      EXIT WHEN p_ref_cursor%NOTFOUND;
 10      IF (addressValue1.city='New York') THEN
 11        addressValue2.line1 :='P.O.Box 2215';
 12        addressValue2.line2 :=null;
 13        addressValue2.city :='New York';
 14        addressValue2.state_code :='NY';
 15        addressValue2.zip :='10020-2215';
 16        addressValue2.country_code :='USA';
 17      ELSIF (addressValue1.city='Bloomington') THEN
 18        addressValue2.line1 :='P.O.Box 6615';
 19        addressValue2.line2 :=null;
 20        addressValue2.city :='Bloomington';
 21        addressValue2.state_code :='IL';
 22        addressValue2.zip :='60610-6615';
 23        addressValue2.country_code :='USA';
 24      ELSIF (addressValue1.city='Vancouver') THEN
 25        addressValue2.line1 :='P.O.Box 0001';
 26        addressValue2.line2 :=null;
 27        addressValue2.city :='Vancouver';
 28        addressValue2.state_code :='NJ';
 29        addressValue2.zip :='08540';
 30        addressValue2.country_code :='USA';
 31      END IF;
 32      PIPE ROW(addressValue2);
 33    END LOOP;
 34    close p_ref_cursor;
 35    RETURN;
 36  END;
 37  /

Function created.

SQL>
SQL> SELECT * FROM TABLE(f_table_plsql2_pipelined(
  2                     CURSOR(SELECT * FROM TABLE(myProc()))));

LINE1                LINE2                CITY                 ST
-------------------- -------------------- -------------------- --
ZIP           COUN
------------- ----
P.O.Box 2215         null                 New York             NY
10020-2215    USA

P.O.Box 2215         null                 New York             NY
10020-2215    USA

P.O.Box 0001         null                 Vancouver            NJ
08540         USA


3 rows selected.

SQL>