-- nested.sql

DROP TABLE emp_email
/

DROP TYPE email_type
/

CREATE TYPE email_type AS TABLE OF VARCHAR2(40)
/

CREATE TABLE emp_email 
(
  id NUMBER,
  email email_type
) NESTED TABLE email STORE AS emp_email_tab
/

INSERT INTO emp_email
 (SELECT id, 
    email_type(lastname || '.' || firstname || '@example.com',
               firstname || '.' || lastname || '@example.com')
  FROM employee JOIN person USING (id))
/

