Object table abased on nested types

CREATE TYPE t_address AS OBJECT (
    street VARCHAR2(15),
    city VARCHAR2(15),
    state CHAR(2),
    zip VARCHAR2(5)
);
/

CREATE TYPE t_person AS OBJECT (
    id INTEGER,
    first_name VARCHAR2(10),
    last_name VARCHAR2(10),
    dob DATE,
    phone VARCHAR2(12),
    address t_address
);
/


SQL> CREATE TABLE emp OF t_person;

Table created.

SQL>


SQL> INSERT INTO emp VALUES (
  2    t_person(1, 'Jason', 'Brown', '01-FEB-1995', '999-555-9999',t_address('Main Street', 'Smalltown', 'CA', '12345'))
  3  );

1 row created.

SQL>

To insert outer type as ordinary columns:

SQL> INSERT INTO emp (id, first_name, last_name, dob, phone,address) VALUES (2,'Jason', 'Green', '05-FEB-1998', '999-999-9999',
  2                  t_address('Main Street', 'Middle Town', 'CA', '12345')
  3  );

1 row created.

SQL>

SQL> SELECT * FROM emp;

        ID FIRST_NAME LAST_NAME  DOB       PHONE         ADDRESS(STREET, CITY, STATE, ZIP)
---------- ---------- ---------- --------- -----------------------------------------------

         1 Jason      Brown      01-FEB-95 999-555-9999 T_ADDRESS('Main Street', 'Smalltown', 'CA', '12345')

         2 Jason      Green      05-FEB-98 999-999-9999 T_ADDRESS('Main Street', 'Middle Town', 'CA', '12345')


SQL>

SQL>
SQL> SELECT * FROM emp e WHERE e.id = 1;

        ID FIRST_NAME LAST_NAME  DOB       PHONE       ADDRESS(STREET, CITY, STATE, ZIP)
---------- ---------- ---------- --------- ---------------------------------------------

         1 Jason      Brown      01-FEB-95 999-555-9999 T_ADDRESS('Main Street', 'Smalltown', 'CA', '12345')


SQL>

SQL> SELECT * FROM emp e WHERE e.address.state = 'CA';

        ID FIRST_NAME LAST_NAME  DOB       PHONE ADDRESS(STREET, CITY, STATE, ZIP)
---------- ---------- ---------- --------- --------------------------------------

         1 Jason      Brown      01-FEB-95 999-555-9999 T_ADDRESS('Main Street', 'Smalltown', 'CA', '12345')
         2 Jason      Green      05-FEB-98 999-999-9999 T_ADDRESS('Main Street', 'Middle Town', 'CA', '12345')


SQL>

SQL> SELECT e.id,
  2         e.first_name,
  3         e.last_name,
  4         e.address.street,
  5         e.address.city,
  6         e.address.state,
  7         e.address.zip
  8  FROM emp e
  9  WHERE e.id = 1;

        ID FIRST_NAME LAST_NAME  ADDRESS.STREET  ADDRESS.CITY    AD ADDRE
---------- ---------- ---------- --------------- --------------- -- -----
         1 Jason      Brown      Main Street     Smalltown       CA 12345

SQL>