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', 'town', '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 )
6 /
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> select * from purchases;
ID CUSTOMER PRODUCT
-----------------------------------------------------------------------------------------
1 000022020818702007EFFB4B20A2177B51CC69DC1F5F9D95B91B624DC88DB51BA9B83230D8 0000220208DC78B0F6B61E431FABCBEF463314969A9109C12B203A4E03B462604D027BE27E
SQL>
SQL> UPDATE purchases SET product = (
2 SELECT REF(op) FROM object_products op WHERE op.id = 2
3 ) WHERE id = 1;
1 row updated.
SQL>
SQL> drop table purchases;
Table dropped.
SQL>
SQL> drop table object_products;
Table dropped.
SQL>
SQL> drop table object_customers;
Table dropped.
SQL> drop type persontype;
Type dropped.
SQL> drop type addresstype;
Type dropped.
SQL>