Oracle Collections & TABLE Expression
Version 11.1
 
General Information
A nested table is a table stored within the structure of another table.
Data Dictionary Objects
collection$ type$ dba_types all_types user_types
System Privileges
ALTER ANY TYPE CREATE TABLE DROP ANY TABLE
CREATE TYPE CREATE ANY TABLE EXECUTE ANY TYPE
CREATE ANY TYPE DROP ANY TYPE UNDER ANY TYPE

Create Types And Table 		You can perform DML operations on nested tables only if they are defined as columns of a table. Therefore, when the query_table_expression_clause of an INSERT, DELETE, or UPDATE statement is a table_collection_expression, the collection expression must be a subquery that uses the TABLE function to select the table's nested table column. The examples that follow are based on the following scenario:

Suppose the database contains a table hr_info with columns department_id, location, and manager_id, and a column of nested table type people which has last_name, department_id, and salary columns for all the employees of each respective manager:
CREATE OR REPLACE TYPE people_typ AS OBJECT (
last_name     VARCHAR2(25),
department_id NUMBER(4),
salary        NUMBER(8,2));
/

desc people_typ

SELECT text
FROM user_source
WHERE name = 'PEOPLE_TYP';

SELECT typecode, attributes, incomplete, final, instantiable
FROM user_types
WHERE type_name LIKE 'PEOPLE%TYP';

CREATE OR REPLACE TYPE people_tab_typ AS TABLE OF people_typ;
/

desc people_tab_typ

SELECT text
FROM user_source
WHERE name = 'PEOPLE_TAB_TYP';

 SELECT typecode, attributes, incomplete, final, instantiable
FROM user_types
WHERE type_name LIKE 'PEOPLE%TYP';

CREATE TABLE hr_info (
department_id NUMBER(4),
location_id   NUMBER(4),
manager_id    NUMBER(6),
people        people_tab_typ)
NESTED TABLE people STORE AS people_stor_tab;

desc hr_info

set describe depth all linenum on indent on

desc hr_info

col data_type format a30

SELECT column_name, data_type
FROM user_tab_cols
WHERE table_name = 'HR_INFO';
 
DML with Collections

Insert 		INSERT INTO hr_info
(department_id, location_id, manager_id, people)
VALUES
(280, 1800, 999, people_tab_typ());

INSERT INTO hr_info
(department_id, location_id, manager_id, people)
VALUES
(205, 2000, 881, people_tab_typ());

SELECT * FROM hr_info;

-- The following example inserts into the people nested
-- table column of hr_info table's department numbered 280:

INSERT INTO TABLE(
  SELECT h.people
  FROM hr_info h
  WHERE h.department_id = 280)
VALUES ('Smith', 280, 1750);

INSERT INTO TABLE(
  SELECT h.people
  FROM hr_info h
  WHERE h.department_id = 205)
VALUES ('Smith', 999, 1750);

COMMIT;

SELECT * FROM hr_info;

-- another insert into the people table nested in hr_info
INSERT INTO TABLE(
  SELECT h.people
  FROM hr_info h
  WHERE h.department_id = 280)
VALUES ('Jones', 280, 919);

SELECT * FROM hr_info;

SELECT cardinality(people)
FROM hr_info;

Update All Nested Rows 		UPDATE TABLE(
SELECT <alias.column_name>
FROM <able_name alias>
WHERE <alias.column_name> = <value)
VALUES (<value, value, value>);
-- Updates department 280's people nested table:

UPDATE TABLE(
  SELECT h.people
  FROM hr_info h
  WHERE h.department_id = 280) p
SET p.salary = p.salary + 100;

SELECT * FROM hr_info;

Update Selected Nested Row 		Updates only Smith's record
UPDATE TABLE(
  SELECT h.people
  FROM hr_info h
  WHERE h.department_id = 280) p
SET p.salary = p.salary - 500
WHERE p.last_name = 'Smith';

SELECT * FROM hr_info;

Select (Collection Unnesting)		 To select data from a nested table column, you use the TABLE function to treat the nested table as the columns of a table. This process is called collection unnesting.

You could get all the rows from hr_info, which was created in the preceding example, and all the rows from the people nested table column of hr_info using the following statement.
SELECT t1.department_id, t2.*
FROM hr_info t1, TABLE(t1.people) t2
WHERE t2.department_id = t1.department_id;
Now suppose that people is not a nested table column of hr_info, but is instead a separate table with columns last_name, department_id, address, hiredate, and salary. You can extract the same rows as in the preceding example with this statement.
CREATE TABLE t (
department_id NUMBER(4),
last_name     VARCHAR2(25),
salary        NUMBER(8,2));

INSERT INTO t
SELECT t1.department_id, t2.last_name, t2.salary
FROM hr_info t1, TABLE(t1.people) t2
WHERE t2.department_id = t1.department_id;

SELECT *
FROM t;

Delete 		Delete from Department 280's people nested table
DELETE TABLE(
  SELECT h.people
  FROM hr_info h
  WHERE h.department_id = 280) p
WHERE p.salary > 1200;
 
Comparing Collections

Comparing Collections 		Collections cannot be directly compared for equality or inequality. For instance, the following IF condition is not allowed:

set serveroutput on

DECLARE
 TYPE clientele IS TABLE OF VARCHAR2(64);
 group1 clientele := clientele('Customer 1', 'Customer 2');
 group2 clientele := clientele('Customer 1', 'Customer 3');
BEGIN
  -- Equality test causes compilation error
  IF group1 = group2 THEN
    dbms_output.put_line('Equal');
  ELSE
    dbms_output.put_line('Not Equal');
  END IF;
END;
/

 This restriction also applies to implicit comparisons. For example, collections cannot appear in a DISTINCT,
GROUP BY, or ORDER BY list. Individual elements, however can be compared. For example:

DECLARE
 TYPE Clientele IS TABLE OF VARCHAR2(64);
 group1 Clientele := Clientele('Customer 1', 'Customer 2');
 group2 Clientele := Clientele('Customer 1', 'Customer 3');
BEGIN
  -- Equality test causes compilation error
  IF group1(1) = group2(1) THEN
    dbms_output.put_line('Equal');
  ELSE
    dbms_output.put_line('Not Equal');
  END IF;
END;
/

and

DECLARE
 TYPE Clientele IS TABLE OF VARCHAR2(64);
 group1 Clientele := Clientele('Customer 1', 'Customer 2');
 group2 Clientele := Clientele('Customer 1', 'Customer 3');
BEGIN
  -- Equality test causes compilation error
  IF group1(2) = group2(2) THEN
    dbms_output.put_line('Equal');
  ELSE
    dbms_output.put_line('Not Equal');
  END IF;
END;
/

so ...

DECLARE
 TYPE Clientele IS TABLE OF VARCHAR2(64);
 group1 Clientele := Clientele('Customer 1', 'Customer 2');
 group2 Clientele := Clientele('Customer 1', 'Customer 3');
BEGIN
  -- Equality test causes compilation error
  FOR i IN 1..2
  LOOP
    IF group1(i) = group2(i) THEN
      dbms_output.put_line('Equal');
    ELSE
      dbms_output.put_line(group1(i) ||
      ' Is Not The Same As ' || group2(i));
    END IF;
  END LOOP;
END;
/
 
Related Topics
Collection Functions
Nested Tables
REF Cursors
Types
VArrays
 
Creating Collections

Create A Collection From An Object		  
CREATE OR REPLACE TYPE people_typ AS OBJECT (
last_name     VARCHAR2(25),
department_id NUMBER(4),
salary        NUMBER(8,2));
/

CREATE OR REPLACE TYPE people_tab_typ AS TABLE OF people_typ;
/

CREATE TABLE hr_info (
department_id NUMBER(4),
location_id   NUMBER(4),
manager_id    NUMBER(6),
people        people_tab_typ)
NESTED TABLE people STORE AS people_stor_tab;

DESC hr_info

INSERT INTO hr_info
VALUES
(100, 1800, 999, people_tab_typ());

INSERT INTO hr_info
VALUES
(200, 2000, 881, people_tab_typ());

SELECT * FROM hr_info;
 
Create Type Header As Database Object

Type Collection Of A User Defined Object Data Type		 CREATE OR REPLACE TYPE <type_name> AS TABLE OF  <user_defined_data_type>;
/
CREATE OR REPLACE TYPE phones_tab_t AS TABLE OF phone_t;
/

SELECT object_name, object_type
FROM user_objects;

SELECT type_name, typecode
FROM user_types;

SELECT type_name, coll_type
FROM user_coll_types;

Heap Table Of A User Defined Data Object Type		 CREATE TABLE <table_name>
column_name <user_defined_data_type);
CREATE TABLE test (
person_id   NUMBER(10),
per_phones  phone_t);

desc test

COL data_type FORMAT a40

SELECT column_name, data_type
FROM user_tab_cols
WHERE table_name = 'TEST';

Heap Table Of With A  User Defined Data Object Type As A Nested Table		 CREATE TABLE <table_name> (
column_name   <user_defined_data_type>)
NESTED TABLE <column_name> STORE AS <name_of_nested_table>;
CREATE TABLE person (
person_id     NUMBER(10),
first_name    VARCHAR2(25),
middle_inits  VARCHAR2(4),
last_name     VARCHAR2(25),
name_suffix   VARCHAR2(5),
title_1       VARCHAR2(5),
title_2       VARCHAR2(5),
dob           DATE,
ssn           VARCHAR2(11),
address_id    NUMBER(10),
person_phones phones_tab_t,
email_address VARCHAR2(30),
ok_to_mail    VARCHAR2(1),
active_flag   VARCHAR2(1))
NESTED TABLE person_phones STORE AS phones_tab
TABLESPACE uwdata;

desc person

COL table_name FORMAT a30
COL table_type_name FORMAT a20
COL parent_table_column FORMAT a25

SELECT table_name, table_type_name, parent_table_column
FROM user_nested_tables;

Insert Into Parent And Nested Table		 INSERT INTO <table_name>
(<column_name_list>)
VALUES
(<list_of_column_values>, <table_type_constructor>(<type_constructor(<type_values>)));
INSERT INTO person
(person_id, last_name, person_phones)
VALUES
(1, 'Morgan', phones_tab_t(phone_t('C', '206-555-1212')));

INSERT INTO person
(person_id, last_name, person_phones)
VALUES
(2, 'Cline', phones_tab_t(phone_t('C', '425-555-1212')));

Insert Into Nested Table		 INSERT INTO TABLE (
SELECT <nested_table_column_name>
FROM <table_name>
WHERE <column_name> <condition <value>)
VALUES
(<column_value>, <column_value>);
INSERT INTO TABLE(
SELECT person_phones
FROM person
WHERE person_id = 1)
VALUES
('H','206-987-6543');

SELECT t1.person_id, last_name, t2.*
FROM person t1, TABLE(t1.person_phones) t2;
Select From Nested Table SELECT <column_name_list> FROM <table_name>;
SELECT * FROM person;
Select From Nested Table With TABLE Function SELECT <column_name_list, <tested_table_column_list>
FROM <table> <alias>, TABLE (<alias.nested_table_name> <alias>;
SELECT person_id, last_name, t2.*
FROM person t1, TABLE(t1.person_phones) t2;

Update All Nested Table Rows		 UPDATE person
SET person_phones =   <table_constructor>(<type_constructor(<column_values>));
UPDATE person
SET person_phones = phones_tab_t(phone_t('H', '360-555-1212'))
WHERE person_id = 2;

Update Selected Nested Table Rows		 UPDATE TABLE (
<nested_select_statment>) <alias>
SET <nested_column> = <value>
WHERE <alias.nested_column_name> <condition> <value>;
UPDATE TABLE (
SELECT person_phones
FROM person
WHERE person_id = 1) p
SET p.phone_type = 'W'
WHERE p.phone_number = '206-987-6543';

SELECT t1.person_id, last_name, t2.*
FROM person t1, TABLE(t1.person_phones) t2;

Delete From Nested Table		 DELETE TABLE (
<nested_select_statment>) <alias>
WHERE <alias.nested_column_name> <condition> <value>;
DELETE TABLE (
SELECT person_phones
FROM person
WHERE person_id = 1) p
WHERE p.phone_number = '206-987-6543';
SELECT a_code FROM TABLE (
SELECT per_phone
FROM phone h) p
WHERE 1=1;
Create Type Header As Database Varray

User Defined Object Data Type		 -- same as above

CREATE OR REPLACE TYPE <type_name> AS OBJECT (
<column_name> <data_type>);
/
CREATE OR REPLACE TYPE title_t AS OBJECT (
title_name VARCHAR2(5));
/

SELECT type_name, typecode
FROM user_types;

Define VARRAY Of Object Type		 CREATE TYPE TitleList AS VARRAY(<integer>) OF <data_type>;
/

SELECT type_name, typecode
FROM user_types;
CREATE TYPE TitleList AS VARRAY(3) OF title_t;
/

SELECT type_name, typecode
FROM user_types;

Heap Table With Nested Varray		 CREATE TABLE <table_name> (
column_name  varray_name);
DROP TABLE person;

CREATE TABLE person (
person_id     NUMBER(10),
first_name    VARCHAR2(25),
middle_inits  VARCHAR2(4),
last_name     VARCHAR2(25),
name_suffix   VARCHAR2(5),
person_titles title_t,
dob           DATE,
ssn           VARCHAR2(11),
address_id    NUMBER(10),
person_phones phones_tab_t,
email_address VARCHAR2(30),
ok_to_mail    VARCHAR2(1),
active_flag   VARCHAR2(1))
NESTED TABLE person_phones STORE AS phones_tab
TABLESPACE uwdata;

desc person

COL parent_table_column FORMAT a30

SELECT parent_table_name, parent_table_column, type_name
FROM user_varrays;
 -- note there is no record

Insert Into Parent, Varray,  And Nested Table		 INSERT INTO <table_name>
(<column_name_list>)
VALUES
(<list_of_column_values>,
<varray_constructor>(<varray_values>) <table_type_constructor>(<type_constructor(<type_values>)));
INSERT INTO person
(person_id, last_name, person_titles, person_phones)
VALUES
(1, 'Morgan', title_t('PhD'),
phones_tab_t(phone_t('C', '206-555-1212')));

INSERT INTO person
(person_id, last_name, person_titles, person_phones)
VALUES
(2, 'Cline', title_t('MD'),
phones_tab_t(phone_t('C', '425-555-1212')));

Insert Into Nested Varray		  


COL person_titles FORMAT a20

SELECT t1.person_id, last_name, person_titles, t2.*
FROM person t1, TABLE(t1.person_phones) t2;
Select From Nested Table SELECT <column_name_list> FROM <table_name>;
SELECT person_id, last_name, person_titles, person_phones
FROM person;
Select From Nested Table With TABLE Function SELECT <column_name_list, <tested_table_column_list>
FROM <table> <alias>, TABLE (<alias.nested_table_name> <alias>
SELECT person_id, last_name, t2.*
FROM person t1,
TABLE(t1.person_phones) t2;

Update All Nested Table Rows		 UPDATE person
SET person_phones =   <table_constructor>(<type_constructor(<column_values>));
UPDATE person
SET person_phones = phones_tab_t(phone_t('H', '360-555-1212'))
WHERE person_id = 2;

Update Selected Nested Table Rows		 UPDATE TABLE (
<nested_select_statment>) <alias>
SET <nested_column> = <value>
WHERE <nested_column_name> <condition> <value>
UPDATE TABLE (
SELECT person_phones
FROM person
WHERE person_id = 1) p
SET p.phone_type = 'W'
WHERE p.phone_number = '206-987-6543';

SELECT t1.person_id, last_name, t2.*
FROM person t1, TABLE(t1.person_phones) t2;

Delete From Nested Table		 DELETE TABLE (
<nested_select_statment>) <alias>
DELETE TABLE (
SELECT person_phones
FROM person
WHERE person_id = 1) p
WHERE p.phone_number = '206-987-6543';
 
 
 
Contact Us • Legal Notices and Terms of Use • Privacy Statement