Mega Code Archive
You can access the rows in the object tables that are pointed to by REF column values using t REF() function; this functio
<![CDATA[
SQL>
SQL> CREATE Or Replace TYPE AddressType AS OBJECT (
2 street VARCHAR2(15),
3 city VARCHAR2(15),
4 state CHAR(2),
5 zip VARCHAR2(5)
6 )
7 /
Type created.
SQL>
SQL> CREATE Or Replace TYPE PersonType AS OBJECT (
2 id NUMBER,
3 first_name VARCHAR2(10),
4 last_name VARCHAR2(10),
5 dob DATE,
6 phone VARCHAR2(12),
7 address AddressType
8 )
9 /
Type created.
SQL>
SQL> CREATE TABLE object_customers OF PersonType
2 /
Table created.
SQL>
SQL> INSERT INTO object_customers VALUES (
2 PersonType(1, 'John', 'White', '04-FEB-1945', '800-555-5555',
3 AddressType('2 Ave', 'City', 'MA', '12345')
4 )
5 );
1 row created.
SQL>
SQL> INSERT INTO object_customers (
2 id, first_name, last_name, dob, phone,
3 address
4 ) VALUES (
5 2, 'James', 'Green', '05-FEB-1968', '800-555-4444',
6 AddressType('3 Ave', 'Town', 'CA', '12345')
7 );
1 row created.
SQL>
SQL> CREATE Or Replace TYPE ProductType AS OBJECT (
2 id NUMBER,
3 name VARCHAR2(15),
4 description VARCHAR2(22),
5 price NUMBER(5, 2),
6 days_valid NUMBER
7 )
8 /
Type created.
SQL>
SQL> CREATE TABLE object_products OF ProductType
2 /
Table created.
SQL>
SQL> INSERT INTO object_products (
2 id, name, description, price, days_valid
3 ) VALUES (
4 1, 'AAA', 'BBB', 2.99, 5
5 );
1 row created.
SQL>
SQL> CREATE TABLE purchases (
2 id NUMBER PRIMARY KEY,
3 customer REF PersonType SCOPE IS object_customers,
4 product REF ProductType SCOPE IS object_products
5 );
Table created.
SQL>
SQL> INSERT INTO purchases (
2 id,
3 customer,
4 product
5 ) VALUES (
6 1,
7 (SELECT REF(oc) FROM object_customers oc WHERE oc.id = 1),
8 (SELECT REF(op) FROM object_products op WHERE op.id = 1)
9 );
1 row created.
SQL>
SQL> select * from purchases;
ID CUSTOMER PRODUCT
-------------------------------------------------------------------------------------------------------------------------------------------------------------
1 000022020861FDF674DA3D4B1088D187CB56924C27CD0C0B2EF27A4E5AB731BDCEE0BF1441 0000220208F8102DD78DC54DD0BB75E4AEA43E91BF05A396E7F9534DAE9CE3FE3F78872A74
SQL>
SQL> SELECT DEREF(customer), DEREF(product)
2 FROM purchases;
DEREF(CUSTOMER)(ID, FIRST_NAME, LAST_NAME, DOB, PHONE, ADDRESS(STREET, CITY, STATE, ZIP))
-------------------------------------------------------------------------------------------
DEREF(PRODUCT)(ID, NAME, DESCRIPTION, PRICE, DAYS_VALID)
------------------------------------------------------------------------------------------
PERSONTYPE(1, 'John', 'White', '04-FEB-45', '800-555-5555', ADDRESSTYPE('2 Ave', 'City', 'MA', '12345'))
PRODUCTTYPE(1, 'AAA', 'BBB', 2.99, 5)
SQL>
SQL> drop table purchases;
Table dropped.
SQL>
SQL> drop table object_products;
Table dropped.
SQL>
SQL> drop table object_customers;
Table dropped.
SQL>
SQL> drop type persontype;
Type dropped.
SQL> drop type addresstype;
Type dropped.
SQL>
]]>