Oracle Collections & TABLE Expression
Version 21c

General Information
Library Note Morgan's Library Page Header
ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.
A nested table is a table stored within the structure of another 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.

Be sure to use the link at page bottom to explore COLLECTION FUNCTIONS.
Collection Characteristics Test
Characteristic Associative Array Nested Table VARRAY
Bounded No Extendable Yes
Condense N/A .TRIM .TRIM
Dimensionality N/A Single Single
Equality Comparisons Single Yes No
Extend Assigned Value By Subscript .EXTEND .EXTEND (to max. size)
EXTEND Elements Yes Must use .EXTEND No
Initialization By Declaration Constructor Constructor
PL/SQL Element
Reference By		 INDEX INTEGER INTEGER
Retains Order When Stored N/A No Yes
SET Operator Compatible No Yes No
Sparse Yes 0After Deletions Only No
Table Column Compatible No Out-of-line Storage In-line Storage
Uninitialized State Not Nullable Atomically NULL Atomically NULL
Use with TABLE Operator No Yes Yes
Data Dictionary Objects
ALL_TYPES CDB_TYPES TYPE$
COLLECTION$ DBA_TYPES USER_TYPES
System Privileges
ALTER ANY TYPE CREATE TABLE DROP ANY TYPE
CREATE ANY TABLE CREATE TYPE EXECUTE ANY TYPE
CREATE ANY TYPE DROP ANY TABLE UNDER ANY TYPE
 
Creating A Collection Based On An Object
Creation of a User Defined Object Data Type and Collection CREATE OR REPLACE TYPE <type_name> AS TABLE OF <user_defined_data_type>;
/
conn uwclass/uwclass@pdbdev

CREATE OR REPLACE TYPE phone_t AUTHID DEFINER AS OBJECT (
a_code   CHAR(3),
p_number CHAR(12));
/

SELECT type_name, typecode, incomplete, final, instantiable
FROM user_types;

CREATE OR REPLACE TYPE phone_tab_t AS TABLE OF phone_t;
/

SELECT type_name, typecode, incomplete, final, instantiable
FROM user_types;

col coll_type format a10

SELECT type_name, coll_type, elem_type_owner, elem_type_name
FROM user_coll_types;
Heap Table Of A User Defined Data Object Type CREATE TABLE <table_name> (
column_name <user_defined_data_type);
conn uwclass/uwclass@pdbdev

CREATE TABLE test (
person_id  NUMBER(10),
per_phones phone_t);

desc test

set describe depth all

desc test

set describe depth 1

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>;
conn uwclass/uwclass@pdbdev

CREATE TABLE person (
person_id     NUMBER(10),
first_name    VARCHAR2(10),
last_name     VARCHAR2(10),
person_phones phone_tab_t,
active_flag   VARCHAR2(1))
NESTED TABLE person_phones STORE AS phones_tab
TABLESPACE uwdata;

desc person

SELECT table_name
FROM user_tables;

select table_name, table_type, table_type_owner, nested
FROM user_all_tables;

col parent_table_column format a25

SELECT table_name, table_type_name, parent_table_column
FROM user_nested_tables;
 
Inserting Into A Collection Based On An Object
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>)));
conn uwclass/uwclass@pdbdev

INSERT INTO person
(person_id, first_name, last_name, person_phones, active_flag)
VALUES
(0, 'Test', 'Tester', phone_tab_t(), 'I');

INSERT INTO person
(person_id, first_name, last_name, person_phones, active_flag)
VALUES
(1, 'Dan', 'Morgan', phone_tab_t(phone_t('C', '206-555-1212')), 'A');

INSERT INTO person
(person_id, first_name, last_name, person_phones)
VALUES
(2, 'Helen', 'Lofstrom', phone_tab_t(phone_t('C', '425-555-1212')));

col person_phones format a50

SELECT * FROM person;
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');
 
Selecting From A Collection Based On An Object
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;
 
Updating A Collection Based On An Object
Update All Nested Table Rows UPDATE person
SET person_phones = <table_constructor>(<type_constructor(<column_values>));
SELECT person_id, last_name, t2.*
FROM person t1, TABLE(t1.person_phones) t2;

UPDATE person
SET person_phones = phone_tab_t(phone_t('H', '360-555-1212'))
WHERE person_id = 2;

SELECT person_id, last_name, t2.*
FROM person t1, TABLE(t1.person_phones) t2;
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.a_code = 'W'
WHERE p.p_number = '206-987-6543';

SELECT t1.person_id, last_name, t2.*
FROM person t1, TABLE(t1.person_phones) t2;
 
Deleting From A Collection Based On An Object
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.p_number = '206-987-6543';

SELECT t1.person_id, last_name, t2.*
FROM person t1, TABLE(t1.person_phones) t2;
 
Collections In PL/SQL
Inserting Into A PL/SQL Collection

The dread "ORA-06531 Reference to uninitialized collection" exception is addresses as shown in the demo at right.		 DECLARE
 -- declare record
 <type_definition_IS_RECORD>;

 -- declare variable of type record
 <declare_IS_RECORD_variable>;

 -- declare table of records
 <type_definition_IS_TABLE_OF>;

 -- declare variable of type table of records and initialize the variable
 <declare_IS_TABLE_OF_variable> := <IS_TABLE_OF_variable(NULL);
BEGIN
  <pl_sql_body>
END;
/
DECLARE
 TYPE isRec_T IS RECORD (
 recId   INT,
 recName dbms_id,
 recDate TIMESTAMP(6));

 TYPE isTabOf_T IS TABLE OF isRec_T;

 isTabOf_A isTabOf_T;
BEGIN
  isTabOf_A(1) := isRec_T(1,'Morgan',SYSTIMESTAMP);
END;
/
DECLARE
*
ERROR at line 1:
ORA-06531: Reference to uninitialized collection
ORA-06512: at line 11

DECLARE
 TYPE isRec_T IS RECORD (
 recId   INT,
 recName dbms_id,
 recDate TIMESTAMP(6));

 isRecVar isRec_T;

 TYPE isTabOf_T IS TABLE OF isRec_T;

 isTabOf_A isTabOf_T := isTabOf_T(NULL);   -- collection initialization
BEGIN
  isRecVar := isRec_T(1, 'Morgan', SYSTIMESTAMP);
  dbms_output.put_line(isRecVar.recName);

  isTabOf_A(1) := isRec_T(1,'Morgan',SYSTIMESTAMP);
  dbms_output.put_line(isTabOf_A(isTabOf_A.FIRST).recId);
  dbms_output.put_line(isTabOf_A(isTabOf_A.FIRST).recName);
  dbms_output.put_line(isTabOf_A(isTabOf_A.FIRST).recDate);
END;
/
 
Creating A Collection Based On a VARRAY
Creation of a User Defined Object Data Type 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, incomplete, final, instantiable
FROM user_types;
VARRAY Creation CREATE TYPE TitleList AS VARRAY(<integer>) OF <data_type>;
/
CREATE TYPE TitleList AS VARRAY(3) OF title_t;
/

SELECT type_name, typecode, incomplete, final, instantiable
FROM user_types;

SELECT parent_table_name, parent_table_column, type_owner, type_name
FROM user_varrays;
-- note there is no record
Create Heap Table With Nested Varray and Collection CREATE TABLE <table_name> (
column_name varray_name);
DROP TABLE person PURGE;

CREATE TABLE person (
person_id    NUMBER(10),
first_name   VARCHAR2(25),
last_name    VARCHAR2(25),
person_title TitleList,
active_flag  VARCHAR2(1))
NESTED TABLE person_title STORE AS title_tab
TABLESPACE uwdata;

CREATE TABLE person (
person_id    NUMBER(10),
first_name   VARCHAR2(25),
last_name    VARCHAR2(25),
person_title TitleList,
active_flag  VARCHAR2(1))
TABLESPACE uwdata;

desc person

set describe depth all

desc person

set describe depth 1

col parent_table_column format a30

SELECT parent_table_name, parent_table_column, type_name
FROM user_varrays;
 
Inserting Into A Collection Based On A VARRAY
Insert Records 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_title, active_flag)
VALUES
(1, 'Dan', 'Morgan', TitleList(title_t('PhD')),'A');

SELECT * FROM person;

INSERT INTO person
(person_id, last_name, person_title, active_flag)
VALUES
(2, 'Tara', 'Havemeyer', TitleList(title_t('MD'), title_t('RN')), 'I');

SELECT person_id, first_name, last_name, person_title, active_flag
FROM person;
 
Selecting From A Collection Based On A VARRAY
Simple Select SELECT <column_name_list> FROM <table_name>;
SELECT person_id, last_name, person_title, active_flag
FROM person;

col person_title format a40

SELECT person_id, last_name, person_title, active_flag
FROM person;
Unnesting Using the TABLE Function SELECT <column_name_list, <tested_table_column_list>
FROM <table> <alias>, TABLE(<alias.nested_table_name> <alias>;
SELECT t1.person_id, t1.last_name, t2.*, t1.active_flag
FROM person t1, TABLE(t1.person_title) t2;
 
Updating A Collection Based On A VARRAY
Update all Nested Table rows associated with a single table row UPDATE <table_name>
SET <column_name> = <table_constructor>(<type_constructor(<column_values>));
UPDATE person
SET person_title = TitleList(title_t('DSc'))
WHERE person_id = 1;
To change the information in a nested table by using the UPDATE statement with a VARRAY, you cannot update individual elements in the varray. You must update all the elements at once - that is, replace the varray. UPDATE TABLE (
<nested_select_statment>) <alias>
SET <nested_column> = <value>
WHERE <nested_column_name> <condition> <value>;
SELECT * FROM person;

UPDATE person
SET person_title = TitleList(title_t('RN'), title_t('MD'))
WHERE person_id = 2;

SELECT * FROM person;
 
Deleting From A Collection Based On A VARRAY
Delete From Nested Table with VARRAY DELETE TABLE (
<nested_select_statment>) <alias>
[WHERE ...];
SELECT * FROM person;

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

SELECT person_id
FROM (
SELECT t1.person_id, t2.*
FROM person t1, TABLE(t1.person_title) t2)
WHERE title_name = 'RN';

DELETE person
WHERE person_id IN (
  SELECT person_id
  FROM (
  SELECT t1.person_id, t2.*
  FROM person t1, TABLE(t1.person_title) t2)
  WHERE title_name = 'RN');

SELECT * FROM person;

DELETE person
WHERE person_id = 1;
 
Comparing Collections
Collection Comparison

Collections cannot be directly compared for equality or inequality.		 -- the following IF conditions 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. */

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;
/
 
Multidimensional Collections
Working with multidimensional collections CREATE OR REPLACE TYPE uw_varray AS VARRAY(5) OF NUMBER;
/

CREATE TABLE mdc_tab (
rid         NUMBER,
demo_varray uw_varray);

desc mdc_tab

INSERT INTO mdc_tab VALUES (1, uw_varray(1,2,3,4,5));
INSERT INTO mdc_tab VALUES (2, uw_varray(10,20,30,40,50));
INSERT INTO mdc_tab VALUES (3, uw_varray(100,200,300,400,500));

SELECT * FROM mdc_tab;

set serveroutput on

DECLARE
 vVAr    uw_varray := uw_varray();
 TYPE rid_t IS TABLE OF mdc_tab.rid%TYPE INDEX BY BINARY_INTEGER;
 rid_a   rid_t;
 TYPE uwv_t IS TABLE OF mdc_tab.demo_varray%TYPE INDEX BY BINARY_INTEGER;
 uwv_a   uwv_t;
 exStr   CLOB := 'SELECT rid, demo_varray FROM mdc_tab';
BEGIN
  EXECUTE IMMEDIATE exStr BULK COLLECT INTO rid_a, uwv_a;
  -- for each row in the table
  FOR i IN rid_a.FIRST .. rid_a.LAST LOOP
    dbms_output.put_line('Accessing array row: ' || TO_CHAR(i));
    -- for each element in the varray
    FOR j IN uwv_a(i).FIRST .. uwv_a(i).LAST LOOP
      dbms_output.put_line(uwv_a(i)(j));
    END LOOP;
  END LOOP;
END;
/
 
Collection Methods
COUNT

Returns the number of elements in the collection		 COUNT RETURN INTEGER
CREATE OR REPLACE TYPE colldemo IS VARRAY(5) OF NUMBER;
/

set serveroutput on

DECLARE
 varr colldemo := colldemo();
BEGIN
  varr := colldemo(1,2,3,4);
  dbms_output.put_line(varr.COUNT);

  varr := colldemo(1,2,3,4,NULL);
  dbms_output.put_line(varr.COUNT);

  varr := colldemo(1,2,3,4,5);
  dbms_output.put_line(varr.COUNT);
END;
/
DELETE

Deletes all or the identified element from a collection. Specific element deletion not available with VARRAYS		 DELETE [<BEG_RANGE_INTEGER>, [<END_RANGE_INTEGER>]];
CREATE OR REPLACE TYPE colldemo IS VARRAY(5) OF NUMBER;
/

set serveroutput on

DECLARE
 varr colldemo := colldemo();
BEGIN
  varr := colldemo(1,2,3,4,5);
  dbms_output.put_line(varr.COUNT);

  varr.DELETE;
  dbms_output.put_line(varr.COUNT);
END;
/
EXISTS

ReturnsTRUE if the element exist, FALSE if it does not		 EXISTS(<INTEGER>) RETURN BOOLEAN
CREATE OR REPLACE TYPE colldemo IS VARRAY(5) OF NUMBER;
/

set serveroutput on

DECLARE
 varr colldemo := colldemo();
BEGIN
  varr := colldemo(1,2,3);

  IF varr.EXISTS(2) THEN
    dbms_output.put_line('Element 2 exists');
  END IF;

  IF NOT varr.EXISTS(4) THEN
    dbms_output.put_line('Element 4 does not exist');
  END IF;
END;
/
EXTEND

Extends the collection by 1 or n elements		 EXTEND [(<INTEGER>)];
CREATE OR REPLACE TYPE colldemo IS VARRAY(5) OF NUMBER;
/

set serveroutput on

DECLARE
 varr colldemo := colldemo();
BEGIN
  varr := colldemo(1);
  dbms_output.put_line(varr.COUNT);

  varr.EXTEND;
  dbms_output.put_line(varr.COUNT);

  varr.EXTEND(3);
  dbms_output.put_line(varr.COUNT);
END;
/
FIRST

Points to the first element of the collection		 FIRST[(<integer>)];
CREATE OR REPLACE TYPE colldemo IS VARRAY(5) OF NUMBER;
/

set serveroutput on

DECLARE
 varr   colldemo := colldemo();
 colval NUMBER;
BEGIN
  varr := colldemo(1,2,3,4,5);

  colval := varr.FIRST;
  dbms_output.put_line(colval);

  colval := varr.NEXT(1);
  dbms_output.put_line(colval);

  colval := varr.NEXT(2);
  dbms_output.put_line(colval);

  colval := varr.NEXT(3);
  dbms_output.put_line(colval);

  colval := varr.LAST;
  dbms_output.put_line(colval);
END;
/
LAST
Points to the last element of the collection		 LAST[(<integer>)];
See FIRST Demo Above
NEXT
Points to the element of the collection indicated		 NEXT [(<INTEGER>)];
See FIRST Demo Above
PRIOR
Points to the element of the collection indicated		 PRIOR [(<INTEGER>)];
See FIRST Demo Above
TRIM

Removes the indicated number of trailing elements from the collection		 TRIM [(<INTEGER>)];
CREATE OR REPLACE TYPE colldemo IS VARRAY(5) OF NUMBER;
/

set serveroutput on

DECLARE
 varr colldemo := colldemo(1,2,3,4,5);
BEGIN
  dbms_output.put_line(varr.COUNT);

  varr.TRIM(2);
  dbms_output.put_line(varr.COUNT);

  varr.TRIM;
  dbms_output.put_line(varr.COUNT);
END;
/
 
XMLTYPE
Converting relational data into XML conn scott/tiger@pdbdev

CREATE OR REPLACE TYPE emp_typ AS OBJECT (
empno NUMBER(4), ename VARCHAR2(10), job VARCHAR2(9), mgr NUMBER(4),
hiredate DATE, sal NUMBER(7,2), comm NUMBER(7,2), deptno NUMBER(2));
/

SELECT XMLTYPE (emp_typ (empno => 1000, ename => NULL, job => NULL,
mgr => NULL, hiredate => SYSDATE, sal => NULL, comm => NULL,
deptno => NULL)) xmlrec
FROM dual;

SELECT XMLTYPE(emp_typ(empno, ename, job, mgr, hiredate, sal, comm, deptno)) xmlrec
FROM emp;
Using a VARRAY as a collection type conn scott/tiger@pdbdev

CREATE OR REPLACE TYPE emptab AS OBJECT (
empno NUMBER(4), ename VARCHAR2(10), job VARCHAR2(9), mgr NUMBER(4),
hiredate DATE, sal NUMBER(7,2), comm NUMBER(7,2), deptno NUMBER(2));
/

CREATE OR REPLACE TYPE emp_typ AS VARRAY(50) OF emptab;
/

SELECT XMLTYPE(value(e))
FROM TABLE(CAST(MULTISET(SELECT * FROM emp WHERE job = 'MANAGER') AS emp_typ)) e;
Using the ANYDATA data type conn scott/tiger@pdbdev

CREATE OR REPLACE TYPE emp_typ AS OBJECT (
empno NUMBER(4), ename VARCHAR2(10), job VARCHAR2(9), mgr NUMBER(4),
hiredate DATE, sal NUMBER(7,2), comm NUMBER(7,2), deptno NUMBER(2));
/

CREATE OR REPLACE TYPE emp_tab AS TABLE OF emp_typ;
/

DECLARE
 CURSOR empcur IS
 SELECT * FROM emp;
BEGIN
  FOR emprec IN empcur LOOP
    dbms_output.put_line(XMLTYPE(ANYDATA.convertcollection(
    emp_tab (emp_typ (emprec.empno, emprec.ename, emprec.job,
    emprec.mgr, emprec.hiredate, emprec.sal, emprec.comm,
    emprec.deptno)))).getstringval());
  END LOOP;
END;
/

Related Topics
Associative Arrays
Built-in Functions
Built-in Packages
Collection Functions
Nested Tables
Object Tables
PLITBLM
REF Cursors
Types
VARRAYs
XMLTYPE
What's New In 21c
What's New In 23c

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2023 Daniel A. Morgan All Rights Reserved
  DBSecWorx