DELETE Commands for Nested Tables and Varrays in Oracle 23ai

Nested tables and varrays are both Oracle PL/SQL collection types, but they have fundamentally different deletion semantics. A nested table supports element-level deletion through the DELETE statement using the TABLE() operator. A varray does not support element-level deletion — its fixed-size structure means that removing elements requires either setting them to NULL via an UPDATE statement or reconstructing the entire varray in a PL/SQL block. Understanding this asymmetry is essential for collection type selection in schema design and for writing correct DML against object-relational tables in Oracle 23ai.

Deleting from a Nested Table

To delete rows from a nested table, use the DELETE statement with the TABLE() operator wrapping a subquery that selects the nested table column from the parent row. The TABLE() operator is the correct syntax in Oracle 23ai. The legacy THE keyword that appeared in earlier Oracle documentation is deprecated and should not be used in new code.

The general syntax for deleting from a nested table is:

DELETE TABLE(SELECT <nested table column>
  FROM <parent table> <table alias>
  WHERE <parent row condition>) <nested table alias>
WHERE <nested row condition>;

The components of this statement are:

Nested Table DELETE — Practical Examples

The following schema demonstrates nested table deletion using a realistic Oracle 23ai object type definition:

CREATE OR REPLACE TYPE project_type AS OBJECT (
    project_id   NUMBER,
    project_name VARCHAR2(100),
    start_date   DATE,
    status       VARCHAR2(20)
);
/

CREATE OR REPLACE TYPE project_list AS TABLE OF project_type;
/

CREATE TABLE employees (
    employee_id   NUMBER PRIMARY KEY,
    employee_name VARCHAR2(100),
    department    VARCHAR2(50),
    projects      project_list
) NESTED TABLE projects STORE AS employee_projects_tab;

To delete all projects from a specific employee's nested table:

DELETE FROM TABLE(
    SELECT e.projects
    FROM employees e
    WHERE e.employee_id = 1001
) nt;

To delete a specific project from an employee's nested table using a filter on the nested row:

DELETE FROM TABLE(
    SELECT e.projects
    FROM employees e
    WHERE e.employee_id = 1001
) nt
WHERE nt.project_id = 5001;

To delete all projects with a specific status across a single employee's record:

DELETE FROM TABLE(
    SELECT e.projects
    FROM employees e
    WHERE e.employee_id = 1001
) nt
WHERE nt.status = 'CANCELLED';

Each of these statements operates on the stored nested table for one parent row. The TABLE() subquery identifies the parent, and the outer WHERE clause filters the nested rows to delete.

Bulk Deletion with FORALL

When the same nested table deletion must be applied across many parent rows, the FORALL statement combined with BULK COLLECT provides the performance-correct pattern in Oracle 23ai. Rather than executing one DELETE per parent row in a PL/SQL loop, FORALL sends the entire set of DML operations to the SQL engine in a single round trip, dramatically reducing context switches between the PL/SQL and SQL engines.

DECLARE
    TYPE id_list IS TABLE OF employees.employee_id%TYPE;
    v_emp_ids id_list;
BEGIN
    -- Collect the parent row identifiers
    SELECT employee_id
    BULK COLLECT INTO v_emp_ids
    FROM employees
    WHERE department = 'ENGINEERING';

    -- Delete cancelled projects for all collected employees
    FORALL i IN 1..v_emp_ids.COUNT
        DELETE FROM TABLE(
            SELECT e.projects
            FROM employees e
            WHERE e.employee_id = v_emp_ids(i)
        ) nt
        WHERE nt.status = 'CANCELLED';

    COMMIT;
END;
/

This pattern is appropriate when batch maintenance operations need to clean nested table data across many parent rows without the overhead of row-by-row PL/SQL processing.

Deleting from a Varray

A varray has a fixed maximum size defined at type creation and does not support element-level deletion through a DELETE statement. Oracle 23ai does not change this constraint. The two available approaches are setting the entire varray to NULL via UPDATE, or reconstructing the varray in a PL/SQL block to produce a version without the unwanted element.

The following schema is used for the varray examples:

CREATE OR REPLACE TYPE manager_type AS OBJECT (
    manager_id   NUMBER,
    manager_name VARCHAR2(100),
    role         VARCHAR2(50)
);
/

CREATE OR REPLACE TYPE manager_list AS VARRAY(5) OF manager_type;
/

CREATE TABLE departments (
    dept_id   NUMBER PRIMARY KEY,
    dept_name VARCHAR2(100),
    managers  manager_list
);

Pattern 1 — Set the Entire Varray to NULL

To remove all elements from a varray for a specific row, set the varray column to NULL using an UPDATE statement:

UPDATE departments
SET    managers = NULL
WHERE  dept_id = 10;

This is the simplest deletion operation available for a varray. It removes all elements in a single DML statement and is appropriate when the entire collection for that row needs to be cleared.

Pattern 2 — Set a Specific Element to NULL

To effectively remove a single element at a known index position, assign NULL to that index:

UPDATE departments
SET    managers(3) = NULL
WHERE  dept_id = 10;

This leaves a NULL placeholder at position 3 within the varray. The varray retains its structure and the other elements are unchanged. Applications consuming this varray must account for NULL elements when iterating.

Pattern 3 — Reconstruct the Varray Without the Unwanted Element

When a specific element must be removed cleanly without leaving a NULL placeholder, the varray must be fetched into a PL/SQL variable, reconstructed without the unwanted element, and written back to the table:

DECLARE
    v_managers manager_list;
BEGIN
    -- Fetch the current varray
    SELECT managers
    INTO   v_managers
    FROM   departments
    WHERE  dept_id = 10;

    -- Reconstruct without the element at index 2
    -- Assumes varray currently has 5 elements
    v_managers := manager_list(
        v_managers(1),
        v_managers(3),
        v_managers(4),
        v_managers(5),
        NULL           -- pad to maintain declared size
    );

    -- Write the reconstructed varray back
    UPDATE departments
    SET    managers = v_managers
    WHERE  dept_id = 10;

    COMMIT;
END;
/

The reconstruction approach requires explicit handling of the varray size. Because a varray's maximum element count is fixed at type creation, the reconstructed instance must not exceed that count. Padding with NULL at the end is one strategy for maintaining the declared size while logically removing an element.

Collection Type Selection — Deletion Requirements

The deletion semantics of each collection type should inform schema design decisions when element-level modification is a frequent operation.

If the application requires frequent element-level deletion, filtering on nested collection attributes, or bulk DML operations across many parent rows, nested tables are the more appropriate collection type. Varrays are better suited to small, ordered, fixed-size collections where the full set is typically replaced rather than selectively modified.

JSON Relational Duality Views as an Alternative

Oracle 23ai introduces JSON Relational Duality Views as an architectural alternative to object-relational collection types for certain use cases. Where a nested table stores child data as a collection column within a parent row, a Duality View exposes a normalized parent-child relational structure as a JSON document. Modification of the child data — including deletion of specific child records — is expressed as a document replacement operation rather than DML on a collection type.

For applications that prefer document-oriented access patterns or need to expose nested data through REST endpoints via Oracle REST Data Services, Duality Views provide a simpler modification model than the TABLE() subquery syntax required for nested table DML. For existing schemas that use nested tables or varrays, the TABLE() approach with DELETE or UPDATE remains the correct and fully supported path in Oracle 23ai.