Querying Nested Tables and Varrays (Oracle Collection Types Explained)

Module Objectives

What Is a Collection?

Collections vs Records — When to Use Each

Records and collections solve different problems. A record — defined with TYPE rec IS RECORD or anchored to a table row with %ROWTYPE — stores one complete set of related attributes: one customer, one order, one product. A collection stores multiple instances of the same type: many phone numbers for one customer, many line items for one order, many measurements for one sensor. When your data is inherently multi-valued for a single parent entity, a collection is the appropriate structure.

Inner Table and Outer Table — The Key Distinction

The most important conceptual distinction for querying Oracle collections is the inner table / outer table relationship. A collection consists of two logical tables that work together.

The inner table is the enclosing relational table — the standard Oracle table in which a nested table or varray appears as a column data type. The inner table has conventional relational columns alongside the collection column.

The outer table is the collection itself — the nested table or varray that is implemented as a column within the inner table. The outer table stores the collection elements. For nested tables, Oracle stores the outer table data out-of-line in a separate storage segment; for varrays, Oracle stores the data inline with the parent row.

Using the schema that appears throughout this module: SALE_HEADER is the inner table — the enclosing relational table with conventional columns (SALE_ID, CUSTOMER) plus the collection column. DETAIL_NEST is the outer table — the nested table column within SALE_HEADER that stores the collection of detail records. DETAIL_TABLE is the storage segment where Oracle physically stores the nested table data out-of-line.

The diagram below (Figure m4-1.1) shows this three-zone relationship: SALE_HEADER (inner table) → DETAIL_NEST column (outer table, expanded) → the TABLE() keyword that flattens the collection into a queryable result set.

The Three Oracle Collection Types

Associative Arrays (Index-By Tables)

Associative arrays — historically called PL/SQL tables and then index-by tables — are purely in-memory PL/SQL structures. They cannot be stored as column types in relational tables, and they cannot be queried directly with SQL. They are indexed by either a unique integer or a unique VARCHAR2 string, making them effective as lookup tables, caches, and temporary data structures within PL/SQL blocks, procedures, and functions.
Associative arrays are declared with the INDEX BY clause:

TYPE phone_lookup_type IS TABLE OF VARCHAR2(20)
    INDEX BY VARCHAR2(50);   -- string-indexed associative array

phone_lookup   phone_lookup_type;
phone_lookup('Gauss')   := '555-0192';
phone_lookup('Riemann') := '555-0317';

Nested Tables

Nested tables are the most flexible Oracle collection type for SQL querying. They can be declared as standalone schema-level types, used as column data types in relational tables, and queried with SQL using the TABLE() operator. Nested tables have no declared maximum size — they can grow to hold any number of elements. Oracle stores nested table data out-of-line in a separate storage segment associated with the parent table, which is why the TABLE() flattening operation is required to query their contents with SQL.
A nested table type and its use in a table definition:

-- Create the nested table type at schema level
CREATE OR REPLACE TYPE detail_table_type AS TABLE OF VARCHAR2(100);
/

-- Create the parent table using the nested table type as a column
CREATE TABLE sale_header (
    sale_id      NUMBER PRIMARY KEY,
    customer     VARCHAR2(100),
    detail_nest  detail_table_type
) NESTED TABLE detail_nest STORE AS detail_table;

The NESTED TABLE ... STORE AS clause names the out-of-line storage segment (detail_table) where Oracle physically stores the nested table elements.

Varrays (Variable-Size Arrays)

A varray is an ordered collection with a declared maximum size. Unlike nested tables, varrays maintain element order and cannot exceed their declared maximum element count. Oracle stores varray data inline with the parent row — in the same data block as the enclosing table row — rather than in a separate storage segment. This inline storage makes varrays efficient for small, fixed-maximum collections where element order matters and the collection is always read as a unit.

-- Create a varray type with a maximum of 5 elements
CREATE OR REPLACE TYPE phone_varray AS VARRAY(5) OF VARCHAR2(20);
/

CREATE TABLE contacts (
    contact_id   NUMBER PRIMARY KEY,
    last_name    VARCHAR2(100),
    phone_numbers  phone_varray
);

Choosing the Right Collection Type

The decision framework for selecting a collection type rests on three questions. First, does the collection need to be stored in a relational table column? If no, use an associative array for in-memory PL/SQL work. If yes, choose between nested tables and varrays. Second, is there a known maximum element count that will never be exceeded? If yes and the collection is small and order matters, use a varray. If the size is unbounded or unknown, use a nested table. Third, will individual elements be deleted independently? Nested tables support deletion of individual elements; varrays do not — you replace the entire varray to modify it.

Collection Storage — Inline vs Out-of-Line

History of Oracle Collections

Enhanced Collection Features

-- Increase the maximum size of phone_varray from 5 to 10
ALTER TYPE phone_varray MODIFY LIMIT 10 CASCADE;

-- Find elements common to both collections
SELECT CAST(MULTISET(
    SELECT * FROM TABLE(collection_a)
    INTERSECT
    SELECT * FROM TABLE(collection_b)
) AS detail_table_type) AS common_elements
FROM dual;

-- Test whether a value is a member of a nested table column
SELECT sale_id, customer
FROM sale_header
WHERE 'Widget A' MEMBER OF detail_nest;

-- Test whether one collection is a subset of another
SELECT sale_id
FROM sale_header s1, sale_header s2
WHERE s1.detail_nest SUBMULTISET OF s2.detail_nest
AND s1.sale_id != s2.sale_id;

Oracle Collection Methods

DECLARE
    TYPE num_table IS TABLE OF NUMBER;
    nums   num_table := num_table(10, 20, 30, 40, 50);
    idx    PLS_INTEGER;
BEGIN
    idx := nums.FIRST;
    WHILE idx IS NOT NULL LOOP
        DBMS_OUTPUT.PUT_LINE('Element ' || idx || ': ' || nums(idx));
        idx := nums.NEXT(idx);
    END LOOP;
END;
/

IF detail_collection.EXISTS(3) THEN
    DBMS_OUTPUT.PUT_LINE(detail_collection(3));
END IF;

detail_collection.EXTEND;               -- add one null element
detail_collection.EXTEND(3);            -- add three null elements
detail_collection.DELETE(2);            -- delete element at index 2 (nested table only)
detail_collection.TRIM(1);              -- remove last element

The TABLE and THE Keywords

-- Oracle 8 syntax — deprecated, do not use in new code
SELECT value(d)
FROM THE (SELECT detail_nest FROM sale_header WHERE sale_id = 1) d;

-- Current syntax — Oracle 9i through 23ai
SELECT d.COLUMN_VALUE AS detail_item
FROM sale_header sh,
     TABLE(sh.detail_nest) d
WHERE sh.sale_id = 1;