Flatten Varray into Nested Table in Oracle (TABLE and CAST Functions)

Why Flatten a Varray?

When Flattening Is the Better Approach

Flattening is preferred over whole retrieval in four common situations. First, when the query needs to filter on element values — returning only the phone numbers that match a certain area code, or only the detail amounts above a threshold. Second, when the query needs to sort elements — ORDER BY on flattened elements produces a sorted list; the whole-collection form returns elements in their storage order without SQL-level sorting control. Third, when the query needs to aggregate — COUNT, SUM, MIN, MAX, and AVG operate on individual rows, not on collection values. Fourth, when element data needs to be joined to other tables — a flattened varray produces rows that can participate in JOIN operations with other relational tables.

Varray Flattening vs Nested Table Flattening — The Same Syntax

The TABLE() correlated join syntax for flattening a varray is identical to the syntax for flattening a nested table covered in lessons 2 and 3. The difference is in the underlying storage model: nested table data is retrieved from an out-of-line storage segment; varray data is retrieved from inline storage within the parent row. TABLE() abstracts this distinction — the SQL developer writes the same correlated join pattern regardless of whether the collection column is a varray or a nested table.

The TABLE() Correlated Form — Flattening Syntax

-- Flatten a varray column into individual queryable rows
-- TABLE() correlated form — Oracle 23ai standard
SELECT t.COLUMN_VALUE AS phone_number
FROM   <object_table_name> obj,
       TABLE(obj.<varray_name>) t
WHERE  <condition on object table>;

Line-by-Line Explanation

The SELECT clause names COLUMN_VALUE aliased as phone_number. COLUMN_VALUE is an Oracle pseudocolumn that refers to the value of each element in the virtual table produced by TABLE() when the collection contains scalar values — VARCHAR2, NUMBER, DATE, or other scalar SQL types. When the collection contains object type instances rather than scalars, the element attributes are accessed using dot notation (t.attribute_name) rather than COLUMN_VALUE.
The FROM clause lists two sources: the object table aliased obj, and TABLE(obj.varray_name) aliased t. For each row of the object table that Oracle processes, TABLE(obj.varray_name) accesses the varray stored inline with that row and presents each element as one row in the virtual table t. The implicit join between obj and t produces one result row per element per parent row — a one-to-many expansion from parent rows to element rows.

The WHERE clause filters on the object table (obj). Placing conditions here — obj.cust_id = 29, for example — narrows which parent rows the TABLE() expansion applies to before any elements are produced. This is the most efficient position for parent-row filtering because it minimizes the number of elements that TABLE() must surface.

COLUMN_VALUE — The Scalar Element Pseudocolumn

COLUMN_VALUE is the name Oracle assigns to the single column of the virtual table produced by TABLE() when the collection's element type is a scalar SQL type. When you define a varray as VARRAY(5) OF VARCHAR2(20), each element is a VARCHAR2(20) scalar. TABLE() wraps those scalars in a virtual table with one column; Oracle names that column COLUMN_VALUE by default.

COLUMN_VALUE can be aliased to any name in the SELECT clause — AS phone_number, AS detail_amount, AS measurement_value — to make the result set more readable. In WHERE clauses, COLUMN_VALUE is referenced through the TABLE() alias: t.COLUMN_VALUE LIKE '555%'.

When the varray's element type is an object type rather than a scalar — for example, a varray of CUSTOMER_TY objects — COLUMN_VALUE is not used. Instead, the individual object attributes are accessed through the TABLE() alias: t.cust_id, t.cust_name. The COLUMN_VALUE pseudocolumn applies only to scalar element types.

WHERE Conditions on the Object Table

WHERE conditions in a TABLE() flattening query can target two levels of data. Conditions on the object table (obj.cust_id = 29) filter which parent rows participate — these are evaluated against CUSTOMER_OBJ_TABLE's relational columns. Conditions on the flattened elements (t.COLUMN_VALUE LIKE '555%') filter which element rows are returned — these are evaluated against the virtual rows produced by TABLE(). Both condition types can be combined in a single WHERE clause using AND:

-- Filter on both parent row and element value
SELECT t.COLUMN_VALUE AS phone_number
FROM   customer_obj_table obj,
       TABLE(obj.phone_list) t
WHERE  obj.cust_id          = 29
AND    t.COLUMN_VALUE LIKE  '555%';

The CAST / MULTISET Form — Varray to Nested Table Type

-- Cast a varray result into a nested table type for richer querying
SELECT t.COLUMN_VALUE AS phone_number
FROM TABLE(
    CAST(<varray_expression> AS <nested_table_type>)
) t;

What CAST Does in This Context

CAST converts a collection value from one collection type to a compatible collection type. When applied to a varray, CAST( AS ) produces a nested table value whose elements are identical to the varray's elements — the same values, the same order, but now typed as a nested table rather than a varray. TABLE() then flattens that nested table value into rows in the same way it flattens a nested table column directly.

The nested_table_type in the CAST must be compatible with the varray's element type — both must have the same element type or a compatible subtype. For the PHONE_LIST varray of VARCHAR2(20) elements, the CAST target would be a nested table type also defined as TABLE OF VARCHAR2(20).

When to Use CAST Instead of the Direct Correlated Form

The direct correlated form (FROM customer_obj_table obj, TABLE(obj.phone_list) t) is the correct choice for the vast majority of varray queries. The CAST form is appropriate in three specific situations.

First, when the query needs to use nested table set operators — MULTISET INTERSECT, MULTISET UNION, MULTISET EXCEPT, MEMBER OF, SUBMULTISET OF — which are defined for nested table types, not for varray types directly. CAST promotes the varray to a nested table, enabling these operators.

Second, when the varray expression comes from a subquery or function return value rather than directly from a column alias. The direct correlated form (TABLE(obj.phone_list)) requires the collection expression to be alias-qualified to a FROM clause source. If the collection comes from a function (TABLE(get_phone_list(29))), the CAST form may be needed to provide the explicit type for TABLE() to process.

Third, when a varray result needs to participate in a MULTISET operation with a nested table result of the same element type — for example, finding the intersection of a varray of phone numbers from one table with a nested table of phone numbers from another table.

Flattening Example — phone_list / customer_obj_table

-- Retrieve individual phone numbers from the phone_list varray
-- for customer 29 — replaces deprecated VALUE()/THE syntax
SELECT t.COLUMN_VALUE AS phone_number
FROM   customer_obj_table obj,
       TABLE(obj.phone_list) t
WHERE  obj.cust_id = 29;

Query Walkthrough

CUSTOMER_OBJ_TABLE is aliased obj. TABLE(obj.phone_list) is aliased t. For the row where cust_id = 29, Oracle accesses the phone_list varray stored inline with that customer row. Because varrays are stored inline with the parent row, Oracle retrieves the varray data in the same I/O as the parent row — there is no out-of-line segment to navigate. TABLE() then presents each phone number string as one row in the virtual table t, with the string value accessible as t.COLUMN_VALUE. The SELECT clause aliases COLUMN_VALUE as phone_number for readability.

Result — One Row per Varray Element

PHONE_NUMBER
--------------------
555-0291
555-0847
555-0134

Comparison — Lesson 4 Whole Retrieval vs Lesson 5 Flattening

The lesson 4 whole retrieval query for the same customer:

-- Lesson 4 form — whole collection as one result value
SELECT phone_list
FROM   customer_obj_table
WHERE  cust_id = 29;

Applying Filters and Sorting to Flattened Varrays

-- Return only phone numbers in the 555 area code
SELECT obj.cust_id, t.COLUMN_VALUE AS phone_number
FROM   customer_obj_table obj,
       TABLE(obj.phone_list) t
WHERE  t.COLUMN_VALUE LIKE '555%'
ORDER BY obj.cust_id, t.COLUMN_VALUE;

ORDER BY on Flattened Elements

Varray elements are stored in insertion order and TABLE() surfaces them in that order by default. An explicit ORDER BY clause controls the output order independently of storage order:

-- Return all phone numbers for customer 29, sorted alphabetically
SELECT t.COLUMN_VALUE AS phone_number
FROM   customer_obj_table obj,
       TABLE(obj.phone_list) t
WHERE  obj.cust_id = 29
ORDER BY t.COLUMN_VALUE ASC;

Aggregation — COUNT, MIN, MAX on Varray Elements

Aggregate functions operate naturally on flattened varray rows. COUNT(*) counts elements; MIN and MAX find boundary values; SUM and AVG apply to numeric element types:

-- Count how many phone numbers each customer has stored
SELECT obj.cust_id, COUNT(t.COLUMN_VALUE) AS phone_count
FROM   customer_obj_table obj,
       TABLE(obj.phone_list) t
GROUP BY obj.cust_id
ORDER BY phone_count DESC;

DML Against Flattened Varrays

-- Update a specific phone number element in the varray
UPDATE TABLE(
    SELECT phone_list
    FROM   customer_obj_table
    WHERE  cust_id = 29
) t
SET    t.COLUMN_VALUE = '555-0999'
WHERE  t.COLUMN_VALUE = '555-0291';

INSERT and DELETE Against Varray Elements via TABLE()

INSERT through TABLE() adds a new element to the varray (subject to the declared maximum size); DELETE through TABLE() removes matching elements. These DML operations enable surgical modification of individual collection elements — a more precise alternative to replacing the entire varray with a new constructor value:

-- Delete a specific phone number from the varray
DELETE FROM TABLE(
    SELECT phone_list
    FROM   customer_obj_table
    WHERE  cust_id = 29
) t
WHERE t.COLUMN_VALUE = '555-0134';

Preferrable Method to Query Varray - Exercise

Preferrable Method to Query Varray - Exercise