Oracle Nested Table
Oracle Nested Table Setup
CREATE TYPE emp_phone_number_typ TABLE OF VARCHAR2(60);

CREATE TABLE employee_details
(employee_id              VARCHAR2(15) NOT NULL,
 employee_first_name      VARCHAR2(25) NOT NULL,
 employee_last_name       VARCHAR2(25) NOT NULL,
 employee_middle_name     VARCHAR2(25),
 employee_active          VARCHAR2(1) DEFAULT 'Y' NOT NULL,
 created_date             DATE DEFAULT SYSDATE NOT NULL,
 updated_date             DATE
);

ALTER TABLE employee_details ADD
(CONSTRAINT employee_id_pk PRIMARY KEY (employee_id)
);

CREATE TABLE employee_phone_nt
(employee_id              VARCHAR2(15) NOT NULL,
 employee_phone_number    emp_phone_number_typ,
 created_date             DATE DEFAULT SYSDATE NOT NULL,
 updated_date             DATE
);

ALTER TABLE employee_phone_nt ADD
(CONSTRAINT employee_id_fk FOREIGN KEY (employee_id) REFERENCES employee_details(employee_id)
);

Nested Table Data Insertion
INSERT INTO employee_details
(employee_id, employee_first_name, employee_last_name, employee_middle_name)
VALUES(10000, 'John', 'Doe', 'X');

INSERT INTO employee_phone_nt(employee_id, employee_phone_number)
VALUES(10000, emp_phone_number_typ('123-123-1234','234-234-2345','345-345-3456'));


Querying A Nested Table - By Unnesting
SELECT
 e1.employee_id,
 e1.employee_first_name, e1.employee_last_name, e1.employee_middle_name,
 e3.*,
 e1.employee_active,
 e1.created_date
FROM  emp_details e1,
      emp_phone_nt e2
 TABLE(e2.employee_phone_number) e3
WHERE e1.employee_id = e2.employee_id;

-- Table Expression in a CURSOR Expression
SELECT
 e1.employee_id,
 e1.employee_first_name,
 e1.employee_last_name,
 e1.employee_middle_name,
 CURSOR(SELECT * FROM TABLE(e2.employee_phone_number)) emp_phone
 e1.employee_active
FROM  emp_details e1,
      emp_phone_nt e2
WHERE e1.employee_id = e2.employee_id;

Oracle registered trademark of Oracle Corporation.

Last Revised on: November 22, 2013

  1419