Mega Code Archive
You can access this object identifier using the REF() function and store the returned objectifier in a REF column
<![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', 'town', 'AA', '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', 'City', '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> select * from purchases;
ID CUSTOMER PRODUCT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 00002202089E1785E0E6A5410BA917BACC9F93530B35C60A986EAC46F580144901E1EC0F94 00002202088CDC8C88E9AB403D85085FAE2649DF6E02D3BDE222D24D1E85421901D500B4A2
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>
]]>