Create or replace type table1_Type as object ( id integer, dateStart date, etc varchar2(20));-- TYPE TABLE1_TYPE compiledcreate table table1 of table1_type;-- table TABLE1 created.Create or replace type table2_type as object( id integer, items varchar2(30), datePurchased varchar2(20), table1_Ref REF table1_type);-- TYPE TABLE2_TYPE compiledcreate table table2 of table2_type;--table TABLE2 created.INSERT INTO table1 VALUES(table1_Type(1, SYSDATE, 'etc1...'));INSERT INTO table1 VALUES(table1_Type(2, SYSDATE, 'etc2...'));SELECT REF(t)FROM table1 tWHERe id = 1;-- [TST.TABLE1_TYPE]DECLARE l_table_1_id_1 REF table1_Type; l_table_1_id_2 REF table1_Type;BEGIN SELECt REF(t) INTO l_table_1_id_1 FROM table1 t WHERe id = 1; SELECt REF(t) INTO l_table_1_id_2 FROM table1 t WHERe id = 2; INSERT INTO table2 VALUES (21, 'item21', SYSDATE, l_table_1_id_1); INSERT INTO table2 VALUES (22, 'item22', SYSDATE, l_table_1_id_2);END;-- anonymous block completedSELECt COUNT(*) FROM table1;-- 2SELECt COUNT(*) FROM table2;-- 2SELECt * FROM table1;SELECt * FROM table2;SELECt *FROM table1 t1JOIN table2 t2ON REF(t1) = t2.table1_Ref;
