Table Functions involving Object Types : table function « Object Oriented « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Object Oriented
3. table function

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> CREATE or replace TYPE temp_adds IS TABLE OF address;
  2  /

Type created.

SQL>
SQL> CREATE OR REPLACE FUNCTION f_table_obj RETURN temp_adds
  2  IS
  3
  4    v_temp_adds temp_adds :=temp_adds();
  5
  6  BEGIN
  7
  8    v_temp_adds.EXTEND(5);
  9
 10    v_temp_adds(1):= address('a',null,'New York','NY','10020');
 11
 12    v_temp_adds(2):= address('S','Blvd','Bloomington','IL','33333');
 13
 14    v_temp_adds(3):= address('1  Dr.',null,'Vancouver','NJ','22222');
 15
 16    v_temp_adds(4):= address('#9','H Avenue','Dallas','TX','11111');
 17
 18    v_temp_adds(5):= address('1 Ct.',null,'Franklin','MA','44444');
 19
 20    RETURN (v_temp_adds);
 21
 22  END;
 23  /

Function created.

SQL> show errors
No errors.
SQL>
SQL> SELECT * FROM TABLE(f_table_obj);

LINE1                LINE2                CITY                 ST
-------------------- -------------------- -------------------- --
ZIP
-------------
a                    null                 New York             NY
10020

S                    Blvd                 Bloomington          IL
33333

1  Dr.               null                 Vancouver            NJ
22222

#9                   H Avenue             Dallas               TX
11111

1 Ct.                null                 Franklin             MA
44444


5 rows selected.

SQL>
SQL>
SQL> drop type address force;

Type dropped.

SQL>
SQL> drop type temp_adds force;

Type dropped.