Retrieving Elements from 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 *
  2  FROM emp
  3  WHERE id = 1;

        ID FIRST_NAME LAST_NAME ADDRESSES(STREET, CITY, STATE, ZIP)
---------- ---------- -----------------------------------------------------------------

         1 Jason      Bond  T_NESTED_TABLE(T_ADDRESS('Main Street', 'Small town', 'CA', '12345'), T_ADDRESS('Second Street', 'Middle Town', 'CA', '54321'))


SQL>

SQL> SELECT id, first_name, last_name, addresses
  2  FROM emp
  3  WHERE id = 1;

        ID FIRST_NAME LAST_NAME  ADDRESSES(STREET, CITY, STATE, ZIP)
---------- ---------- ------------------------------------------------------------------------------------------

         1 Jason      Bond
T_NESTED_TABLE(T_ADDRESS('Main Street', 'Small town', 'CA', '12345'), T_ADDRESS('Second Street', 'Middle Town', 'CA', '54321'))


SQL>

SQL> SELECT addresses
  2  FROM emp
  3  WHERE id = 1;

ADDRESSES(STREET, CITY, STATE, ZIP)
--------------------------------------------------------------------------------

T_NESTED_TABLE(T_ADDRESS('Main Street', 'Small town', 'CA', '12345'), T_ADDRESS(
'Second Street', 'Middle Town', 'CA', '54321'))


SQL>
SQL>