|
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.
|