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', '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', 'Town', 'CC', '12345')
7 );
1 row created.
SQL>
SQL> select * from object_customers;
ID FIRST_NAME LAST_NAME DOB PHONE ADDRESS(STREET, CITY, STATE, ZIP)
--------------------------------------------------------------------------
1 John White 04-FEB-45 800-555-5555 ADDRESSTYPE('2 Ave', 'City', 'AA', '12345')
2 James Green 05-FEB-68 800-555-4444 ADDRESSTYPE('3 Ave', 'Town', 'CC', '12345')
SQL>
SQL> SELECT *
2 FROM object_customers oc
3 WHERE oc.address.state = 'MA';
no rows selected
SQL>
SQL> drop table object_customers;
Table dropped.
SQL> drop type persontype;
Type dropped.
SQL> drop type addresstype;
Type dropped.
SQL>