Delete Command to remove all Rows from nested Table or varray

In Oracle 19c, when dealing with collections, such as nested tables and VARRAYs, deleting all rows requires different approaches compared to traditional relational tables. Below are the proper methods to remove all elements from a nested table and a VARRAY.

1. Deleting All Rows from a Nested Table:
   A nested table is a type of collection that behaves similarly to a table but is stored within a column in another table.
   • Approach 1: Using the DELETE Method:
     You can use the `.DELETE` method to remove all rows from a nested table.
     Example:

     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 nested table
         DBMS_OUTPUT.PUT_LINE('All elements deleted from nested table.');
     END;
     /
     

2. Deleting All Rows from a VARRAY:
   A "VARRAY (Variable-Size Array)" has a fixed upper bound and does not allow sparse deletions like a nested table.
   • Approach 1: Reinitialize the VARRAY
     VARRAYs do not support the `.DELETE` method, so the best way to remove all elements is to reinitialize it.
     Example:
     

     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 the VARRAY, effectively deleting all elements
         DBMS_OUTPUT.PUT_LINE('All elements deleted from VARRAY.');
     END;
     /
     

Alternative: Updating a Column Containing a Nested Table
If a nested table column is stored inside a relational table, you can update it to empty the nested table.
Example:

UPDATE employees
SET projects = CAST(NULL AS project_nested_table)
WHERE employee_id = 1001;

Where:

• projects is a nested table column in the employees table.
• project_nested_table is the nested table type.

Summary

• projects is a nested table column in the employees table.
• project_nested_table is the nested table type.

In Oracle 19c, the `DELETE` statement does not support the use of the `THE` or `TABLE` keyword in standard SQL syntax. However, there are specific scenarios where the `TABLE` keyword can be used, particularly when dealing with nested tables.

1. Using DELETE with THE
   • The THE keyword was used in older Oracle versions with nested tables, but it is deprecated and no longer necessary in modern versions, including Oracle 19c.
   • Instead, Oracle 19c allows you to directly reference the nested table column.

   Example of deprecated syntax (not recommended in Oracle 19c):

       DELETE FROM THE (SELECT nested_table_column FROM parent_table WHERE condition);
       

   Recommended modern equivalent (Oracle 19c):

       DELETE FROM TABLE (SELECT nested_table_column FROM parent_table WHERE condition);
       

2. Using DELETE with TABLE
   • The TABLE keyword is still valid in Oracle 19c only when dealing with collections, such as nested tables or VARRAYs.
   • It allows you to delete rows from a nested table column inside another table.

   Example:

       DELETE FROM TABLE (SELECT nested_table_column FROM parent_table WHERE condition);
       

Conclusion:

• DELETE FROM THE (...) is deprecated and no longer needed in Oracle 19c.
• DELETE FROM TABLE (...) is still viable but is only used for deleting elements from a nested table collection.

To delete all the data from a nested table, you can use the DELETE statement with the THE or the TABLE keyword, as following example shows:

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

The DML statement above deletes all the elements from a nested table.
You can also delete all the elements by updating the nested table to NULL, as the following example shows:

UPDATE SALE_HEADER
SET detail_nest = NULL
WHERE sale_id = 35);

The DML statement above updates the nested table to NULL, which in turn deletes all the elements within the table.Now that you have learned few techniques for deleting rows from nested tables and varrays, try them yourself with the following tutor-based exercise. In this exercise, you will be asked to build a DELETE statement to delete the records from the SALE_HEADER table.

Click the Exercise link below to build the DELETE statement.
Delete Nested Table - Exercise