Deleting All Rows from a Nested Table or Varray in Oracle 23ai

Lesson 7 covered element-level deletion — removing specific rows from a nested table or setting individual varray elements to NULL. This lesson covers full collection deletion: removing all elements from a nested table or a varray in Oracle 23ai. As with element-level operations, nested tables and varrays require different approaches due to their different storage architectures.

There are four approaches covered in this lesson:

1. Nested table — DELETE FROM TABLE() without an outer WHERE clause
2. Nested table — UPDATE SET col = NULL to null the entire nested table column
3. Varray — UPDATE SET col = NULL to remove all elements
4. Varray — PL/SQL reinitialization using the empty constructor

Approach 1 — Nested Table: DELETE FROM TABLE() Without a WHERE Clause

The most direct approach for removing all elements from a nested table stored in a database column is the DELETE FROM TABLE() statement without an outer WHERE clause. The TABLE() operator identifies the parent row through its subquery, and omitting the outer filter removes all nested table rows for that parent:

DELETE FROM TABLE(
    SELECT d.detail_nest
    FROM   sale_header d
    WHERE  d.sale_id = 35
);

This statement removes all rows from the detail_nest nested table for sale ID 35. The parent row in SALE_HEADER is not affected — it remains in the table with its detail_nest column in an initialized but empty state. The nested table still exists for that row and can be populated again using INSERT INTO TABLE() without requiring reinitialization.

Contrast this with Lesson 7, where the outer WHERE clause was used to target specific nested table rows. Removing the outer WHERE clause applies the deletion to all nested rows for the identified parent, making this a complete rather than selective deletion.

Approach 2 — Nested Table: UPDATE SET col = NULL

An alternative approach for removing all nested table data is to set the nested table column to NULL via a standard UPDATE statement against the parent table:

UPDATE sale_header
SET    detail_nest = NULL
WHERE  sale_id = 35;

This statement sets the detail_nest column to NULL for sale ID 35. The result is different from Approach 1 in an operationally important way:

• After Approach 1 (DELETE FROM TABLE()): the nested table column is initialized but empty. Subsequent INSERT INTO TABLE() and TABLE() DML will work correctly without any reinitialization step.
• After Approach 2 (UPDATE SET col = NULL): the nested table column is NULL. The collection no longer exists for that row. Subsequent TABLE() DML will fail until the column is reinitialized with the constructor — for example, SET detail_nest = detail_table().

Choose Approach 1 when the nested table will be repopulated later. Choose Approach 2 when the intent is to permanently remove all collection data and the column should remain NULL.

NULL vs Empty — A Critical Distinction

The difference between a NULL collection and an empty initialized collection has practical consequences for all subsequent DML operations against that column:

Approach 3 — Varray: UPDATE SET col = NULL

Varrays do not support the DELETE FROM TABLE() syntax — only nested tables can be targeted through the TABLE() operator. To remove all elements from a varray column at the SQL level, set the column to NULL using a standard UPDATE:

UPDATE customer_obj_table
SET    phone_array = NULL
WHERE  cust_id = 25;

This sets the phone_array varray column to NULL for customer ID 25. All phone number data for that customer is removed. As noted in the table above, the column is now NULL — to add elements back, the column must first be reinitialized using the type constructor in a subsequent UPDATE:

UPDATE customer_obj_table
SET    phone_array = phone_array_type()
WHERE  cust_id = 25;

This restores the column to an empty, non-NULL initialized state. Elements can then be added through the PL/SQL fetch-extend-update pattern from Lesson 6.

Approach 4 — Varray: PL/SQL Reinitialization

For in-memory varray variables within PL/SQL blocks, the correct approach for removing all elements is to reinitialize the variable using the empty constructor. Varrays do not support the .DELETE collection method:

DECLARE
    TYPE emp_varray IS VARRAY(5) OF VARCHAR2(100);
    emp_names emp_varray := emp_varray('Alice', 'Bob', 'Charlie');
BEGIN
    emp_names := emp_varray();  -- reinitializes to empty non-NULL varray
    DBMS_OUTPUT.PUT_LINE('Varray reinitialized to empty.');
END;
/

The assignment emp_names := emp_varray() replaces the current varray with a new empty instance of the same type. The variable is now a valid, non-NULL, zero-element varray that can be extended and populated using EXTEND and element assignment.

Note that this operation only affects the in-memory PL/SQL variable. To persist the reinitialization to the database, the variable must be written back to the table via UPDATE:

DECLARE
    v_phones phone_array_type := phone_array_type();
BEGIN
    UPDATE customer_obj_table
    SET    phone_array = v_phones
    WHERE  cust_id = 25;
    COMMIT;
END;
/

PL/SQL .DELETE Method for Nested Table Variables

For in-memory nested table variables in PL/SQL, the .DELETE collection method removes all elements from the variable:

DECLARE
    TYPE emp_nested_table IS TABLE OF VARCHAR2(100);
    emp_names emp_nested_table := emp_nested_table('Alice', 'Bob', 'Charlie');
BEGIN
    emp_names.DELETE;  -- removes all elements from the in-memory variable
    DBMS_OUTPUT.PUT_LINE('Count after DELETE: ' || emp_names.COUNT);
END;
/

After .DELETE, emp_names.COUNT returns 0 and the variable is an initialized empty nested table. However, as with the varray reinitialization above, .DELETE on a PL/SQL variable does not affect the database. To persist the deletion, write the emptied variable back to the database column via UPDATE, or use the DELETE FROM TABLE() SQL approach directly against the stored nested table.

Full Deletion Comparison

Delete Nested Table - Exercise

Delete Nested Table - Exercise