| Lesson 3 | How to flatten a nested table |
| Objective | Write a query that flattens a nested table |
Query that Flattens a Nested Table in Oracle (TABLE Function Example)
The Flattening Query — SALE_HEADER and DETAIL_TABLE
What the Query Must Do
The SALE_HEADER table has a nested table column — DETAIL_NEST — whose element type is DETAIL_TABLE_TYPE. Each row in SALE_HEADER has its own collection of detail records stored out-of-line in the DETAIL_TABLE segment. The goal of the flattening query is to retrieve the detail records for a specific sale — sale_id = 101 — as individual queryable rows, returning the product_id, item_qty, and detail_amount attributes of each detail element.Without flattening, a SELECT against SALE_HEADER returns the DETAIL_NEST column as an opaque collection reference — a single cell, not a set of rows. The TABLE() function bridges the gap between the relational parent row and the out-of-line nested table data, producing a virtual table that the outer query treats as an ordinary join source.
The Complete Flattening Query
The correlated join form is the standard Oracle 23ai pattern for flattening a nested table. The inner table (SALE_HEADER) is aliased; the alias is used inside TABLE() to correlate the nested table column to the current parent row:
-- Flatten DETAIL_NEST for sale_id = 101
-- Correlated TABLE() form — Oracle 23ai standard
SELECT oc.product_id,
oc.item_qty,
oc.detail_amount
FROM sale_header sh,
TABLE(sh.detail_nest) oc
WHERE sh.sale_id = 101;Query Walkthrough
The SELECT clause requests three columns — product_id, item_qty, and detail_amount — from the flattened nested table, referenced through the oc alias. These are attributes of the DETAIL_TABLE_TYPE element type. If columns from the parent row are also needed (sale_id, customer), they are added to the SELECT list prefixed with sh.
The FROM clause specifies two sources: SALE_HEADER aliased sh, and TABLE(sh.detail_nest) aliased oc. For each row of SALE_HEADER that Oracle processes, TABLE(sh.detail_nest) navigates to that row's nested table data in the out-of-line DETAIL_TABLE segment and presents the elements as rows in the oc virtual table. The implicit join between sh and oc produces one result row per detail element.
The WHERE clause (sh.sale_id = 101) filters SALE_HEADER to the single parent row for sale 101. All detail elements belonging to sale 101 are returned — there is no additional filter on the outer table elements in this query. The result is a flat, row-oriented result set showing every product, quantity, and amount from the nested detail collection of sale 101.
To add a filter on the nested table elements — returning only detail rows where detail_amount exceeds a threshold, for example — add an AND condition referencing the oc alias:
-- Flatten DETAIL_NEST and filter on element attributes
SELECT oc.product_id,
oc.item_qty,
oc.detail_amount
FROM sale_header sh,
TABLE(sh.detail_nest) oc
WHERE sh.sale_id = 101
AND oc.detail_amount > 50;Querying Nested Tables with REF Columns
What DEREF() Does
In some Oracle object-relational schemas, a column in a nested table element type is defined as a REF — a typed pointer to a row in another object table — rather than a scalar value such as NUMBER or VARCHAR2. When product_id is a REF to a PRODUCT_TY object in a PRODUCTS object table, selecting oc.product_id returns the REF value itself (an internal pointer), not the product data. To retrieve the actual object that the REF points to, Oracle provides the DEREF() function.DEREF() dereferences a REF column, returning the full object instance that the REF points to. It is the SQL equivalent of following a pointer in a procedural language — the function navigates from the REF value to the actual object it references and returns that object's attributes as a single structured value in the result set.
When product_id Is a REF Rather Than a Scalar
The legacy SALE_HEADER schema in this module's source material defined product_id as a REF column — meaning it points to a row in an object table rather than storing a product ID number directly. The flattening query for this REF-based schema uses DEREF() to retrieve the referenced product object alongside the scalar columns:
-- Flatten DETAIL_NEST where product_id is a REF column
-- DEREF() retrieves the referenced product object
SELECT DEREF(oc.product_id) AS product,
oc.item_qty,
oc.detail_amount
FROM sale_header sh,
TABLE(sh.detail_nest) oc
WHERE sh.sale_id = 34;DEREF vs Scalar — Choosing the Right Column Reference
Use a plain column reference (oc.product_id) when the nested table element type stores scalar values — NUMBER, VARCHAR2, DATE — directly in its attributes. The value returned is the stored scalar itself.
Use DEREF(oc.product_id) when the attribute is defined as a REF to an object type. Without DEREF(), the query returns the raw REF locator — an internal Oracle pointer that is not directly useful in application output. With DEREF(), the query returns the full object instance, whose individual attributes can be accessed using dot notation in the SELECT clause: DEREF(oc.product_id).product_name, DEREF(oc.product_id).unit_price.
In schemas designed for Oracle 23ai from scratch, REF columns are uncommon — direct foreign key relationships and JSON Relational Duality Views are the modern alternatives for cross-table references. DEREF() appears in legacy object-relational schemas built on Oracle 8i and 9i object table patterns and remains supported in Oracle 23ai for backward compatibility.
Defining Nested Table Types
CREATE OR REPLACE TYPE Syntax
Before a nested table column can be used in a CREATE TABLE statement, its element type must be defined as a schema-level object type, and the nested table collection type must be defined as a TABLE OF that element type. The syntax for defining a nested table type is:CREATE OR REPLACE TYPE type_name
AS TABLE OF element_type [ NOT NULL ];Applying the Syntax to the Module Schema
The SALE_HEADER schema requires two type definitions: first, an object type for the individual detail element; second, a nested table type whose elements are instances of that object type:
-- Step 1: Define the element object type
CREATE OR REPLACE TYPE detail_item_ty AS OBJECT (
product_id NUMBER,
item_qty NUMBER,
detail_amount NUMBER(10,2)
);
/
-- Step 2: Define the nested table collection type
CREATE OR REPLACE TYPE detail_table_nt AS TABLE OF detail_item_ty;
/
-- Step 3: Create the parent table using the nested table type
CREATE TABLE sale_header (
sale_id NUMBER PRIMARY KEY,
customer VARCHAR2(100),
detail_nest detail_table_nt
) NESTED TABLE detail_nest STORE AS detail_table;Naming Conventions — TY, NT, NT_TAB
Consistent naming conventions for collection-related objects make schemas self-documenting. The most widely used convention applies three suffixes that encode an object's role:
The TY suffix identifies an object type — the element type of a collection. DETAIL_ITEM_TY is an object type; its name signals that it defines the structure of one detail item.
The NT suffix identifies a nested table collection type — a TABLE OF some element type. DETAIL_TABLE_NT is a nested table type; its name signals that it is a collection (specifically a nested table) of DETAIL_ITEM_TY elements.
The NT_TAB suffix identifies the out-of-line storage segment for a nested table — the segment named in the STORE AS clause. DETAIL_TABLE is the storage segment for DETAIL_TABLE_NT; some schemas name this DETAIL_TABLE_NT_TAB to make the relationship to the collection type explicit.
When these conventions are applied consistently, the name of any object reveals its role and its relationship to related objects. If you encounter a table named DETAIL_TABLE_NT_TAB, you immediately know it is the out-of-line storage for a nested table type named DETAIL_TABLE_NT, whose element type is DETAIL_ITEM_TY. Naming discipline at schema creation time eliminates ambiguity during querying, debugging, and maintenance.
Working with Nested Tables in PL/SQL
Declaring and Initializing a Nested Table Variable
Nested table types defined at the schema level can be used as PL/SQL variable types in blocks, procedures, and functions. A nested table variable must be initialized — either with a constructor call or by fetching from a table — before its elements can be assigned or read. An uninitialized nested table variable is atomically null; attempting to access its elements raises COLLECTION_IS_NULL.The following PL/SQL block declares a DETAIL_TABLE_NT variable, initializes it with two detail items using the constructor, and prints each element's attributes:
DECLARE
-- Declare a nested table variable of the schema-level type
v_details detail_table_nt;
v_idx PLS_INTEGER;
BEGIN
-- Initialize with a constructor — two detail elements
v_details := detail_table_nt(
detail_item_ty(1001, 2, 29.99),
detail_item_ty(1042, 1, 15.50)
);
-- Iterate using FIRST and NEXT
v_idx := v_details.FIRST;
WHILE v_idx IS NOT NULL LOOP
DBMS_OUTPUT.PUT_LINE(
'Item ' || v_idx ||
' | product_id: ' || v_details(v_idx).product_id ||
' | qty: ' || v_details(v_idx).item_qty ||
' | amount: ' || v_details(v_idx).detail_amount
);
v_idx := v_details.NEXT(v_idx);
END LOOP;
END;
/Iterating with FIRST and NEXT
The WHILE loop using FIRST and NEXT is the standard iteration pattern for nested tables in PL/SQL. FIRST returns the index of the first element; NEXT(n) returns the index of the element after index n, or NULL when n is the last element. The loop terminates when NEXT returns NULL — which occurs naturally after the last element is processed.This pattern is preferred over a numeric FOR loop (FOR i IN 1..v_details.COUNT LOOP) for nested tables because nested tables support element deletion — after a DELETE(n) call, element n no longer exists and its index is skipped. NEXT correctly handles the sparse index sequence that results from deletions; a numeric FOR loop would raise NO_DATA_FOUND when it encounters a deleted index.
The Unconstrained Size Distinction vs Varrays
Nested tables are single-dimensional, sequentially indexed collection structures with no declared maximum size. They differ from varrays in two fundamental ways that affect both PL/SQL usage and SQL querying.First, nested tables are unbounded — they can hold any number of elements, limited only by available memory or storage. Varrays have a declared maximum size specified at type creation time (VARRAY(n)) that cannot be exceeded without an ALTER TYPE.
Second, nested tables support sparse deletion — individual elements can be deleted by index using DELETE(n), leaving gaps in the index sequence. The collection remains valid with those gaps; COUNT reflects only the existing elements, not the range of indexes. Varrays do not support individual element deletion; the entire varray must be replaced to remove an element. This deletion behavior makes nested tables better suited for collections that change in membership over time, and makes varrays better suited for fixed-membership ordered collections where element order is always maintained.
Out-of-Line Storage — Recap and Query Implications
How Oracle Stores Nested Table Data
When a table is created with a nested table column and a STORE AS clause, Oracle creates a separate storage segment for the nested table data. The parent table (SALE_HEADER) stores its conventional columns — sale_id, customer — in its own data blocks. The nested table elements — the detail_item_ty instances in DETAIL_NEST — are stored in a separate segment (DETAIL_TABLE) with a hidden NESTED_TABLE_ID column that links each element to its parent row in SALE_HEADER.Oracle maintains the link between parent rows and their nested table elements automatically. When a row is inserted into SALE_HEADER with a DETAIL_NEST constructor value, Oracle writes the element data to the DETAIL_TABLE segment and stores the linking identifier in both places. When a parent row is deleted, Oracle cascades the deletion to the associated nested table elements in the DETAIL_TABLE segment.
The STORE AS Clause
The STORE AS clause in CREATE TABLE names the out-of-line segment for a nested table column:CREATE TABLE sale_header (
sale_id NUMBER PRIMARY KEY,
customer VARCHAR2(100),
detail_nest detail_table_nt
) NESTED TABLE detail_nest STORE AS detail_table;-- Index on the hidden linking column speeds up TABLE() correlated joins
CREATE INDEX idx_detail_table_ntid
ON detail_table (NESTED_TABLE_ID);Why the Correlated Join Is the Efficient Access Pattern
The correlated TABLE() join (FROM sale_header sh, TABLE(sh.detail_nest) oc WHERE sh.sale_id = 101) is the most efficient SQL pattern for nested table access because it allows Oracle's query optimizer to apply the inner table filter (sh.sale_id = 101) before navigating the out-of-line storage. With the filter applied first, Oracle retrieves only one parent row's NESTED_TABLE_ID, uses that ID to navigate directly to the matching elements in DETAIL_TABLE, and returns only those elements — avoiding a full scan of the DETAIL_TABLE segment.The WHERE IN subquery form (WHERE 78 IN (SELECT detail_amount FROM TABLE(sh.detail_nest))) must evaluate TABLE(sh.detail_nest) for every row of SALE_HEADER before the outer WHERE condition can be applied, making it less efficient for large tables. The correlated join form, with an inner table filter in the WHERE clause, is the preferred pattern for all production nested table queries.
In the next lesson, you will learn the easiest way to query a varray.