Simple Varray Query in Oracle (SELECT the Collection as a Whole)

What Is a Varray?

Varray vs Nested Table — The Storage Difference That Simplifies Querying

The fundamental reason that querying a varray is simpler than querying a nested table comes down to storage architecture. Nested table data is stored out-of-line in a separate segment, linked to the parent row through a hidden NESTED_TABLE_ID column. To query nested table elements with SQL, the TABLE() function must navigate that out-of-line storage boundary — hence the correlated join syntax covered in lessons 2 and 3.

Varray data is stored inline. When Oracle reads the parent row from CUSTOMER_OBJ_TABLE, the PHONE_LIST varray data travels with that row — it is part of the same physical storage unit. A simple SELECT of the varray column name retrieves the entire collection in one operation. No TABLE() is needed because there is no out-of-line boundary to cross. The varray arrives as a single structured value containing all its elements, ready to be processed by the application or by a PL/SQL block.

The Simplest Varray Query

Syntax — SELECT the Varray Column

The syntax for a simple varray retrieval query follows the standard three-clause SELECT structure:

SELECT <varray name>
FROM table_name
WHERE <condition for selection>;

What the Result Looks Like — The Whole Collection as One Value

When this query executes, Oracle returns one row for each parent table row that matches the WHERE condition. The varray column appears as a single cell in the result set — not as multiple rows. In Oracle SQL Developer and SQL*Plus, the varray value is displayed using its constructor expression syntax. For a PHONE_LIST_TY varray containing two phone numbers, the result cell displays as:

PHONE_LIST_TY('555-0192', '555-0317')

Querying a Varray — Example

-- Define the varray type — maximum 5 phone numbers per customer
CREATE OR REPLACE TYPE phone_list_ty AS VARRAY(5) OF VARCHAR2(20);
/

-- Define the customer object type using the varray
CREATE OR REPLACE TYPE customer_ty AS OBJECT (
    cust_id      NUMBER,
    cust_name    VARCHAR2(100),
    phone_list   phone_list_ty
);
/

-- Create the object table
CREATE TABLE customer_obj_table OF customer_ty;

SELECT phone_list
FROM customer_obj_table
WHERE cust_id = 26;

DECLARE
    v_phones   phone_list_ty;
BEGIN
    -- Fetch the entire varray into a PL/SQL variable
    SELECT phone_list
    INTO   v_phones
    FROM   customer_obj_table
    WHERE  cust_id = 26;

    -- Access individual elements by index (1-based)
    DBMS_OUTPUT.PUT_LINE('Primary phone:   ' || v_phones(1));

    IF v_phones.COUNT >= 2 THEN
        DBMS_OUTPUT.PUT_LINE('Secondary phone: ' || v_phones(2));
    END IF;

    -- Iterate all elements using COUNT
    FOR i IN 1..v_phones.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE('Phone ' || i || ': ' || v_phones(i));
    END LOOP;
END;
/

Single-Dimensional SQL Collections — Figure m4-4.1

The inverted tree diagram below (Figure m4-4.1) illustrates the structure of a single-dimensional SQL collection and how individual elements are accessed by index. The root node represents the collection variable and its type — in this case, CUST_INTERACTIONS, a nested table collection. Each branch represents one indexed element of the collection, holding a value of a specific SQL datatype.

The Inverted Tree Structure

The inverted tree representation captures two key properties of single-dimensional collections. First, the collection has a single root — the collection variable itself — from which all elements branch. This single root means the collection is a flat, one-level structure: there are no nested levels, no sub-collections at each node. Second, each leaf node represents one element with its own index and its own datatype value. In Oracle 23ai, those element datatypes can include NUMBER, VARCHAR2, DATE, JSON, and user-defined object types — the diagram's three nodes show NUMBER (1001), JSON (interaction details), and VARCHAR2(200) ('Feedback form received') as concrete examples of the multi-type flexibility available in modern Oracle collections.

Sequential vs Non-Sequential Indexing

Varrays and nested tables use sequential integer indexes starting at 1. The index increments by one for each element — element 1, element 2, element 3 — enabling traversal by a simple FOR loop from 1 to COUNT. This sequential structure is what the inverted tree represents: each branch is one step in a numbered sequence.

Associative arrays — the third Oracle collection type introduced in lesson 1 — support non-sequential indexing. An associative array can be indexed by any integer (including negative numbers and non-contiguous values) or by unique VARCHAR2 strings. This makes associative arrays suitable for in-memory lookup tables keyed by a business identifier rather than a position number. The trade-off is that associative arrays are PL/SQL-only — they cannot be stored as column types in relational tables. Varrays and nested tables can be stored in table columns and are therefore the collection types relevant to SQL querying.

Three Types of Oracle Collections

Collection Categories: Arrays and Lists

Lists — No Physical Limit (Nested Tables)

Lists in Oracle's collection model are nested tables. They have no declared maximum size and grow dynamically as elements are added. The practical upper bound on a nested table's size is available storage in the out-of-line segment, not a schema-level constraint. This makes nested tables appropriate for collections whose size cannot be predicted at design time — order line items, audit log entries, sensor readings accumulated over time.

The SGA Memory Constraint

When collections are used as in-memory PL/SQL structures — associative arrays, or nested table and varray variables populated in PL/SQL blocks — their practical size is constrained by the memory available in the System Global Area (SGA). Very large in-memory collections that approach SGA memory limits will cause performance degradation or ORA-04031 errors. For collections that may grow very large, storing them as persistent table columns with out-of-line nested table storage is more memory-efficient than holding them in PL/SQL variables.