Using TABLE() with a Nested Table

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

CREATE TYPE t_nested_table AS TABLE OF t_address;
/



CREATE TABLE emp (
    id INTEGER PRIMARY KEY,
    first_name VARCHAR2(10),
    last_name VARCHAR2(10),
    addresses t_nested_table_address
)

INSERT INTO emp VALUES (1, 'Jason', 'Bond',t_nested_table(
                                                t_address('Main Street', 'Small town', 'CA', '12345'),
                                                t_address('Second Street','Middle Town','CA', '54321')
                                           )
);

SQL> SELECT a.*
  2  FROM emp c, TABLE(c.addresses) a
  3  WHERE id = 1;

STREET          CITY            ST ZIP
--------------- --------------- -- -----
Main Street     Small town      CA 12345
Second Street   Middle Town     CA 54321

SQL>

SQL> SELECT a.street, a.state
  2  FROM emp c, TABLE(c.addresses) a
  3  WHERE id = 1;

STREET          ST
--------------- --
Main Street     CA
Second Street   CA

SQL>

SQL> SELECT c.id, c.first_name, c.last_name, a.*
  2  FROM emp c, TABLE(c.addresses) a
  3  WHERE c.id = 1;

        ID FIRST_NAME LAST_NAME  STREET          CITY            ST ZIP
---------- ---------- ---------- --------------- --------------- -- -----
         1 Jason      Bond       Main Street     Small town      CA 12345
         1 Jason      Bond       Second Street   Middle Town     CA 54321

SQL>