Lesson 7

This module covered the complete query lifecycle for Oracle collection types — nested tables and varrays — from the conceptual inner/outer table model through SQL flattening with TABLE(), whole-collection varray retrieval, varray flattening with CAST/MULTISET, and PL/SQL cursor-based retrieval with nested FOR LOOPs. Six lessons built on each other, starting from collection fundamentals and ending with a complete PL/SQL procedure. This conclusion synthesizes the key concept from each lesson and shows how they connect into a coherent querying framework.

Oracle 23ai supports three collection types: nested tables, varrays, and associative arrays. Nested tables and varrays can be stored as column types in relational tables and queried with SQL. Associative arrays are PL/SQL-only in-memory structures — they cannot be stored in table columns or queried with SQL. The choice between nested tables and varrays depends on three questions: does the collection need persistent SQL storage? Is there a known maximum element count? Will individual elements be deleted independently?

The foundational conceptual distinction for querying is the inner/outer table relationship. The inner table is the enclosing relational table — SALE_HEADER — which contains conventional columns alongside the collection column. The outer table is the collection itself — DETAIL_NEST — which stores the collection elements. For nested tables, Oracle stores outer table data out-of-line in a separate segment; for varrays, Oracle stores data inline with the parent row. This storage difference determines why TABLE() is required for nested table queries and why simple SELECT retrieval works for varrays.

Oracle collection history spans from PL/SQL tables in Oracle 7 (1992) through nested tables and varrays in Oracle 8 (1997), THE deprecation and TABLE() standardization in Oracle 9i (2001), ANSI SQL 2003 multiset operations in Oracle 10g (2003), to native JSON support and JSON Relational Duality Views in Oracle 21c and Oracle 23ai. The core TABLE() flattening syntax is stable across all modern Oracle releases.

Key lesson: Querying Nested Tables and Varrays (Oracle Collection Types Explained)

Flattening collapses the hierarchical relationship between a nested table column and its out-of-line storage segment into a flat, row-oriented result set that SQL can process with standard SELECT, WHERE, and ORDER BY operations. Without flattening, the nested table column is opaque to SQL — Oracle treats it as a single cell containing a collection reference, not as a set of queryable rows. TABLE() provides the explicit bridge: it navigates the out-of-line storage, retrieves the collection elements, and presents them as a virtual table.

Oracle 8i introduced four collection functions for nested table DML and querying: THE (deprecated since Oracle 8.1.6, unsupported in Oracle 23ai), CAST (converts collection types), MULTISET (converts subquery results to collection values), and TABLE (the current standard). THE is documented in this module because it appears in legacy Oracle 8 and 8i codebases — recognizing it is necessary; writing it in new code is not.

The TABLE() function accepts an alias-qualified collection column and returns a virtual table whose rows are the collection elements. The correlated join form (FROM sale_header sh, TABLE(sh.detail_nest) oc) is preferred over the subquery form (WHERE 78 IN (SELECT detail_amount FROM TABLE(sh.detail_nest))). Both are valid Oracle 23ai syntax; the correlated form is more efficient for selective queries because the inner table filter reduces the number of elements TABLE() must surface.

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

The correlated TABLE() join is the Oracle 23ai standard for flattening nested table columns into individual queryable rows. The query joins the inner table (SALE_HEADER aliased sh) to TABLE(sh.detail_nest) aliased oc, producing one result row per detail element per matching parent row. WHERE conditions target two layers simultaneously: inner table conditions (sh.sale_id = 101) filter which parent rows participate; outer table conditions (oc.detail_amount > 50) filter which collection elements are returned.

When a nested table element type defines a REF column — a typed pointer to a row in another object table — the DEREF() function retrieves the referenced object rather than the raw pointer value. DEREF() is supported in Oracle 23ai for backward compatibility with Oracle 8i and 9i object-relational schemas; new schemas should use foreign key relationships or JSON Relational Duality Views instead.

Nested table type definitions require schema-level object types. The three-suffix naming convention (TY for object types, NT for nested table collection types, NT_TAB for out-of-line storage segments) makes schemas self-documenting: DETAIL_ITEM_TY defines one element, DETAIL_TABLE_NT is a TABLE OF DETAIL_ITEM_TY, DETAIL_TABLE is the STORE AS segment. Indexing the hidden NESTED_TABLE_ID column on the out-of-line segment speeds up TABLE() correlated join queries by enabling direct navigation from parent row to elements.

Key lesson: Query that Flattens a Nested Table in Oracle (TABLE Function Example)

The simplest varray query selects the varray column by name — no TABLE(), no flattening, no correlated join. Because varrays are stored inline with the parent row, Oracle retrieves the entire collection in the same I/O operation that reads the row. The result is a single cell containing the entire varray as a structured constructor value: PHONE_LIST_TY('555-0192','555-0317').

This whole-collection retrieval is appropriate when the application or PL/SQL block needs all elements and will process them programmatically after retrieval — iterating by index using v_phones(1), v_phones(2), and so on. It is not appropriate when SQL-level filtering, sorting, or aggregation on individual element values is needed — those operations require the flattening approach covered in lesson 5.

Oracle 23ai collections fall into two categories: arrays (varrays — fixed maximum size, ordered, inline storage) and lists (nested tables — no maximum size, supports sparse deletion, out-of-line storage). Associative arrays, the third collection type, are PL/SQL-only in-memory structures indexed by integer or VARCHAR2 — they cannot be stored in table columns and are not part of the SQL querying patterns covered in this module. The SGA memory constraint applies to all in-memory collection variables; very large collections should be stored as persistent nested table columns rather than held in PL/SQL variables.

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

The TABLE() correlated join syntax for flattening a varray is identical to the syntax for flattening a nested table — TABLE() abstracts the storage model difference. The result is one row per varray element rather than one row containing the entire collection. This element-level access enables SQL-level filtering (WHERE t.COLUMN_VALUE LIKE '555%'), sorting (ORDER BY t.COLUMN_VALUE), and aggregation (COUNT, MIN, MAX on element values) that the whole-collection form cannot provide.

COLUMN_VALUE is the Oracle pseudocolumn that refers to the scalar element value in the virtual table produced by TABLE() when the collection contains scalar types (VARCHAR2, NUMBER, DATE). COLUMN_VALUE can be aliased in the SELECT clause; for object type elements, individual attributes are accessed through the TABLE() alias using dot notation instead.

The CAST form (SELECT t.COLUMN_VALUE FROM TABLE(CAST(varray_expression AS nested_table_type)) t) converts a varray to a nested table type before TABLE() processes it. This form is appropriate when the query needs nested table set operators (MULTISET INTERSECT, MEMBER OF), when the varray comes from a subquery or function rather than a column alias, or when varray results need to participate in MULTISET operations with nested table results. The direct correlated form is correct for the vast majority of varray queries. TABLE() can also be used in DML statements — UPDATE, INSERT, DELETE — against varray columns, enabling surgical modification of individual elements without replacing the entire collection.

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

The SQL TABLE() correlated join is the correct tool when the goal is to retrieve, filter, or aggregate collection element data in a single query. A PL/SQL cursor block is the correct tool when the retrieval involves row-by-row logic that SQL cannot express: conditional branching, formatted multi-line output combining parent-row and element attributes, exception handling per row, or calling stored subprograms for each retrieved record.

Querying SALE_HEADER's DETAIL_NEST nested table with PL/SQL requires two nested loops because the data has two dimensions: SALE_HEADER rows (the outer dimension, traversed by the cursor loop) and DETAIL_NEST elements within each sale (the inner dimension, traversed by the FOR loop). A single loop cannot traverse both dimensions simultaneously.

The explicit cursor pattern — OPEN / FETCH / EXIT WHEN %NOTFOUND / CLOSE — is the foundational PL/SQL cursor construct. %TYPE anchoring (sale_header.sale_id%TYPE) guarantees that variable types remain consistent with column definitions across schema changes. The cursor FOR LOOP is the Oracle 23ai preferred form for straightforward iteration: it handles OPEN, FETCH, and CLOSE implicitly, leaving only the loop body. DBMS_OUTPUT.PUT_LINE is a development and testing tool; production data retrieval should use OUT parameters, REF CURSOR parameters, or pipelined table functions.

Key lesson: PL/SQL Procedure for Nested Table Queries (Cursor and FOR LOOP Pattern)

Having completed this module, you are now able to:

1. Explain the concept of flattening a nested table — flattening collapses the hierarchical inner/outer table relationship into a flat, row-oriented result set using the TABLE() function
2. Describe the SQL enhancements for nested tables — TABLE() is the current Oracle 23ai standard; THE was deprecated in Oracle 8.1.6 and is not used in new development; CAST and MULTISET extend the collection operator set
3. Query nested tables and varrays — using the correlated TABLE() join form for element-level access and direct SELECT for whole-varray retrieval
4. Create a PL/SQL block to query nested tables and varrays — using explicit cursors with nested FOR LOOPs, %TYPE-anchored variables, and DBMS_OUTPUT for formatted output

Flattening — the operation of converting a nested table column's hierarchical, out-of-line storage into a flat, row-oriented result set that SQL can process with standard operators. Flattening is performed by the TABLE() function in the correlated join form. The term applies to both nested tables (where data crosses an out-of-line storage boundary) and varrays (where data is retrieved inline but presented as individual rows for element-level SQL access).

TABLE() — the Oracle 9i through 23ai standard function for flattening collection columns into virtual tables. Accepts an alias-qualified collection column expression and returns a virtual table whose rows are the collection's elements. Replaces the deprecated THE function.

COLUMN_VALUE — the Oracle pseudocolumn name for the single column in the virtual table produced by TABLE() when the collection's element type is a scalar SQL type (VARCHAR2, NUMBER, DATE). Not used when the element type is an object type.

Inner table — the enclosing relational table that contains the collection column as one of its column types (SALE_HEADER). Has conventional relational columns alongside the collection column.

Outer table — the collection itself, implemented as a column within the inner table (DETAIL_NEST). For nested tables, stored out-of-line in a separate segment. For varrays, stored inline with the parent row.

In the next module, you will learn the different ways to modify data within object tables. You will also learn how to insert, update, and delete records from object tables.