PL/SQL   «Prev  Next»

Lesson 3Inserting Rows into a Nested Table
ObjectiveWrite SQL INSERT commands to add data to a nested table in Oracle 23ai using both the single-statement constructor approach and the two-step TABLE() operator approach.

Inserting Rows into a Nested Table in Oracle 23ai

A nested table in Oracle is a collection type that allows a column in a parent table to store a set of rows, effectively acting as a table within a table. Each row in the nested table is linked to a specific parent row through a hidden NESTED_TABLE_ID column — the same 16-byte identifier described in Lesson 2. Nested tables are useful for modeling one-to-many relationships within a single table structure, such as storing multiple order line items for a single sale record.

Inserting data into a nested table requires specific SQL syntax. Oracle provides two approaches: a single INSERT statement that populates both the parent row and the nested table simultaneously using a type constructor, and a two-step process that creates the parent row first with an empty nested table and then adds elements incrementally using the TABLE() operator.

The Type Constructor

Both insertion methods rely on the type constructor — a function automatically created by Oracle for every user-defined collection type. The constructor name matches the collection type name exactly. For a nested table type named DETAIL_TABLE, the constructor is DETAIL_TABLE().

The constructor accepts zero or more element arguments:

The distinction between an empty constructor DETAIL_TABLE() and a NULL nested table is important. A NULL nested table cannot be referenced in a subsequent INSERT INTO TABLE() statement. Always use the empty constructor when the parent row is created before nested table data is available.

Method 1 — Single INSERT with Populated Constructor

The first method inserts the parent row and its nested table elements in a single INSERT statement. The type constructor initializes the nested table inline within the VALUES clause.

The schema for these examples uses a SALE_HEADER table with a nested table column DETAIL_NEST of type DETAIL_TABLE. Each element of DETAIL_TABLE is of type DETAIL_TY, which holds a product reference, item quantity, and detail amount.

INSERT INTO sale_header
VALUES (
    35,
    (SELECT REF(c) FROM customer_obj_table c WHERE cust_id = 1),
    55,
    1.65,
    61.9,
    5.25,
    DATE '2025-12-15',
    DETAIL_TABLE(
        DETAIL_TY(NULL, 12, 35.5),
        DETAIL_TY(20,   1,  55.0)
    )
);

Each component of this statement serves a specific purpose:

This method is appropriate when all nested table data is known at the time the parent row is created and can be expressed as a constructor expression within a single statement.

Method 2 — Two-Step INSERT Using TABLE()

The second method separates parent row creation from nested table population. This approach is useful when nested table data is not available at the time the parent row is created, when elements are inserted incrementally over time, or when nested table data is sourced from a separate query against another table.

Step 1 — Create the Parent Row with an Empty Nested Table

Insert the parent row using an empty constructor to initialize the nested table as a non-NULL empty collection:

INSERT INTO sale_header
VALUES (
    36,
    (SELECT REF(c) FROM customer_obj_table c WHERE cust_id = 1),
    55,
    1.65,
    61.9,
    5.25,
    DATE '2025-12-15',
    DETAIL_TABLE()
);

The DETAIL_TABLE() empty constructor ensures the DETAIL_NEST column is initialized as an empty collection rather than NULL. This is required before the TABLE() operator can reference it in Step 2.

Step 2 — Insert Elements Using the TABLE() Operator

Add rows to the nested table for sale ID 36 using the INSERT INTO TABLE() syntax:

INSERT INTO TABLE (
    SELECT s.detail_nest
    FROM   sale_header s
    WHERE  s.sale_id = 36
)
SELECT REF(p), 200, 56
FROM   product_obj_table p
WHERE  product_id = 21;

The TABLE() operator wraps a subquery that identifies the specific parent row — in this case, the sale with ID 36. The outer INSERT then treats the nested table as a target table and inserts the result of the SELECT as a new element. The REF(p) function retrieves a reference to the product object in PRODUCT_OBJ_TABLE where product_id = 21, along with 200 items and $56.

Additional elements can be inserted into the same nested table by repeating Step 2 with different filter conditions or source queries. Each execution of the INSERT INTO TABLE() statement adds one or more rows to the storage table for that parent row.

Choosing Between the Two Methods

Method 1 — Single INSERT with Constructor Method 2 — Two-Step INSERT
All nested table data known at INSERT time Nested table data arrives after parent row creation
Data expressed as constructor literals Data sourced from another table via SELECT
Simpler — one statement for parent and nested rows More flexible — supports incremental population
Suitable for small, known element sets Suitable for large or dynamically sourced element sets

Verifying the Table Structure

Before writing INSERT statements against an unfamiliar nested table schema, query the Oracle data dictionary to confirm the collection type name and structure. The USER_TAB_COLUMNS view shows column data types for tables owned by the current user:

SELECT column_name, data_type
FROM   user_tab_columns
WHERE  table_name = 'SALE_HEADER';

Sample output:

COLUMN_NAME      DATA_TYPE
---------------  ----------------
SALE_ID          NUMBER
CUSTOMER_REF     REF CUSTOMER_TY
QUANTITY         NUMBER
DISCOUNT         NUMBER
TOTAL_AMOUNT     NUMBER
TAX              NUMBER
SALE_DATE        DATE
DETAIL_NEST      DETAIL_TABLE

The DETAIL_NEST column shows DETAIL_TABLE as its data type, confirming it uses a user-defined collection type. To verify that DETAIL_TABLE is a nested table rather than a varray, query USER_TYPES:

SELECT typecode, attributes
FROM   user_types
WHERE  type_name = 'DETAIL_TABLE';

Sample output:

TYPECODE   ATTRIBUTES
---------  ----------
COLLECTION          0

The TYPECODE value of COLLECTION confirms the type is a nested table. This information is required before constructing INSERT statements — the constructor name must match the type name exactly.

Verifying the NESTED_TABLE_ID

After inserting elements using Method 2, the hidden NESTED_TABLE_ID can be queried to confirm the association between the parent row and its nested table storage. This is the same 16-byte identifier described in Lesson 2 that Oracle uses to correlate parent rows with their nested table elements in the storage table:

SELECT nested_table_id
FROM   TABLE (
    SELECT detail_nest
    FROM   sale_header
    WHERE  sale_id = 36
);

Sample output:

NESTED_TABLE_ID
----------------------------------------
DEE8F406C4B911D39B07000000000001

The presence of the NESTED_TABLE_ID value confirms that the nested table rows for sale ID 36 are correctly stored in the storage table and linked to the parent row. If the nested table were NULL rather than an initialized empty collection, this query would return no rows and the TABLE() operator in subsequent DML would fail.

The next lesson covers inserting data into a varray, where the insertion model differs because the entire collection is stored as a single unit within the parent row rather than as separate rows in a storage table.


SEMrush Software 3 SEMrush Banner 3