Flattening Nested Tables in Oracle (TABLE Function and THE Deprecation)

What Is Flattening?

Why Flattening Is Necessary

Oracle stores nested table data out-of-line. When you define a table with a nested table column and specify NESTED TABLE detail_nest STORE AS detail_table, Oracle creates a separate storage segment (detail_table) for the nested table elements. Each element in that segment carries a hidden NESTED_TABLE_ID column that links it to its parent row in SALE_HEADER. The physical storage boundary between SALE_HEADER and its DETAIL_TABLE segment is what makes flattening necessary: SQL cannot cross that boundary automatically. The TABLE() function provides the explicit bridge — it tells Oracle to traverse the link from the parent row to the out-of-line segment and surface the elements as queryable rows.

Connection to the Inner/Outer Table Model

In lesson 1, SALE_HEADER was identified as the inner table (the enclosing relational table) and DETAIL_NEST as the outer table (the nested table column). Flattening operates on this relationship directly. The flattening query joins the inner table to the result of TABLE(outer column), producing a combined result set where each inner table row is paired with each of its corresponding outer table elements — a one-to-many expansion. The result looks like a single flat table even though the underlying data lives in two separate storage structures.

Oracle Collection Functions — THE, CAST, MULTISET, TABLE

THE — Deprecated Since Oracle 8.1.6

THE was the original Oracle 8 mechanism for querying a nested table column. It wrapped a subquery that returned a single nested table value and mapped that value to a virtual database table. THE retrieves the value of each element of the nested table within the outer table — allowing INSERT, UPDATE, DELETE, and SELECT operations against nested table elements using a subquery inside the THE clause.
THE was deprecated effective Oracle 8.1.6 and is not supported in Oracle 23ai. Its syntax required a correlated subquery wrapped in THE(), which was verbose and error-prone. All new Oracle development should use TABLE() instead. THE appears in this lesson because it is documented in legacy Oracle 8 and Oracle 8i textbooks and codebases — recognizing it on sight is necessary; writing it in new code is not.

TABLE — The Current Standard

TABLE() is the Oracle 9i replacement for THE and remains the current standard through Oracle 23ai. TABLE() maps a collection to a virtual database table that SQL can query directly. Unlike THE, TABLE() accepts an alias-qualified collection column as its argument — enabling the compact correlated join form that is the preferred syntax in modern Oracle development. The TABLE() function returns the contents of the collection as a virtual table, with each collection element presented as one row.

CAST — Converting Collections to SQL Types

CAST converts a collection value from one collection type to another compatible SQL type, or converts a multiset subquery result to a declared collection type. CAST is used when a collection needs to be passed to a function or stored in a column that expects a different but compatible collection type, or when constructing a collection value from a SELECT result for insertion into a nested table column:

-- CAST a subquery result into a nested table type
INSERT INTO sale_header (sale_id, customer, detail_nest)
VALUES (
    10,
    'D. Kim',
    CAST(MULTISET(SELECT item FROM staging_items WHERE order_id = 10)
         AS detail_table_type)
);

MULTISET — Converting Subquery Results to Collections

MULTISET converts the result of a subquery into a collection value of the appropriate nested table type. MULTISET is typically used in combination with CAST when inserting or updating nested table columns from relational query results. It is also used in multiset set operations (MULTISET UNION, MULTISET INTERSECT, MULTISET EXCEPT) introduced in Oracle 10g as ANSI SQL 2003 extensions:

-- MULTISET set operation — elements in collection_a but not collection_b
SELECT sale_id,
       (detail_nest MULTISET EXCEPT other_detail) AS unique_items
FROM sale_header
WHERE sale_id = 1;

The TABLE() Function in Detail

-- Correlated form (preferred — Oracle 9i through 23ai)
SELECT sh.sale_id, oc.product_id, oc.detail_amount
FROM sale_header sh,
     TABLE(sh.detail_nest) oc
WHERE sh.sale_id = 22;

-- Subquery form (valid but less readable)
SELECT sale_id, detail_total
FROM sale_header sh
WHERE 78 IN (SELECT detail_amount FROM TABLE(sh.detail_nest));

Flattening Syntax — Oracle 23ai

SELECT <expression-list>
FROM <inner table> t,
     TABLE(t.<outer column name>) oc
WHERE <condition on inner table>
AND   <condition on outer table elements>;

Flattening Example — SALE_HEADER / DETAIL_NEST

-- Query nested table detail rows using TABLE()
-- Replaces deprecated THE syntax
SELECT oc.product_id,
       oc.item_qty,
       oc.detail_amount
FROM sale_header sh,
     TABLE(sh.detail_nest) oc
WHERE sh.sale_id      = 22
AND   oc.detail_amount = 78;

The WHERE IN Subquery Form

-- Return inner table rows whose nested collection contains at least one
-- element where detail_amount = 78
SELECT sale_id, detail_total, tax_amount, shipping_amount, sale_total
FROM sale_header sh
WHERE 78 IN (SELECT detail_amount FROM TABLE(sh.detail_nest));