|
SQL>
SQL>
SQL> CREATE OR REPLACE TYPE address AS OBJECT
2 (line1 VARCHAR2(20),
3 line2 VARCHAR2(20),
4 city VARCHAR2(20),
5 state_code VARCHAR2(2),
6 zip VARCHAR2(13));
7 /
Type created.
SQL>
SQL> CREATE or replace TYPE temp_adds IS TABLE OF address;
2 /
Type created.
SQL>
SQL> CREATE OR REPLACE FUNCTION myProc
2 RETURN temp_adds PIPELINED
3 IS
4 addressValue address;
5 BEGIN
6 FOR i IN 1..5 LOOP
7 IF (i=1) THEN
8 addressValue := address('St.',null,'York','NY','22222');
9 ELSIF (i=2) THEN
10 addressValue := address('Suite','Blvd','B','IL','33333');
11 ELSIF (i=3) THEN
12 addressValue := address('1 Dr.',null,'P','NJ','33333');
13 ELSIF (i=4) THEN
14 addressValue := address('#9','Avenue','Dallas','TX','11111');
15 ELSIF (i=5) THEN
16 addressValue := address('1 Ct.',null,'F','MA','44444');
17 END IF;
18 PIPE ROW(addressValue);
19 END LOOP;
20 RETURN;
21 END;
22 /
Function created.
SQL> show errors
No errors.
SQL>
SQL> SELECT * FROM TABLE(myProc);
LINE1 LINE2 CITY ST
-------------------- -------------------- -------------------- --
ZIP
-------------
St. null York NY
22222
Suite Blvd B IL
33333
1 Dr. null P NJ
33333
#9 Avenue Dallas TX
11111
1 Ct. null F MA
44444
5 rows selected.
SQL>
SQL> drop type address force;
Type dropped.
SQL>
SQL> drop type temp_adds force;
Type dropped.
|