Mega Code Archive
A pipelined Table Function that returns a PLSQL type
<![CDATA[
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 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 :='Bloomington';
19 addressValue.state_code :='IL';
20 addressValue.zip :='11111';
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> SELECT * FROM TABLE(myProc);
LINE1 LINE2 CITY ST
-------------------- -------------------- -------------------- --
ZIP COUN
------------- ----
20 St. null New York NY
10020 USA
Suite 206 P Blvd Bloomington IL
11111 USA
1 Dr. null Vancouver NJ
22222 USA
3 rows selected.
SQL>
]]>