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 0000220208F78428F66B3B4357A7E2EE26FB982C67E070E7307E2E4FE6A60A03B82FDF9C49 0000220208473CFDED543D404C920B9AB1E2A37337F5BD01A93C83419084C662D11524FB08
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>