Write delete Command for nested table or Varray

Deleting data from a nested table requires the use of a DELETE statement, whereas deleting data from a varray requires the value of the varray to be set to NULL via an UPDATE statement.
To drop a particular nested table, you can use the DELETE statement with the THE or the TABLE keyword, as the following example demonstrates:

DELETE TABLE (SELECT d.detail_nest FROM SALE_HEADER d 
WHERE d.SALE_ID = 35) p 
WHERE p.item_qty = 1;

This DML statement selects a particular record from the nested table for a particular record within the main table and deletes the record. To delete the elements of a varray, the varray must be set to NULL, as the following example shows:

UPDATE CUSTOMER_OBJ_TABLE
SET phone_array = NULL
WHERE cust_id = 25;

The DML statement above deletes all the elements of a varray. The following diagram shows the syntax of deleting data from a nested table:

Here's how you can write DELETE commands for both nested tables and varrays in Oracle PL/SQL:
1. Nested Table:
Let's assume we have a table named `EMPLOYEES` with a nested table column `PROJECTS`.

CREATE OR REPLACE TYPE project_type AS OBJECT (
   project_id NUMBER,
   project_name VARCHAR2(100)
);

CREATE OR REPLACE TYPE project_list AS TABLE OF project_type;

CREATE TABLE EMPLOYEES (
   employee_id NUMBER,
   employee_name VARCHAR2(100),
   projects project_list
) NESTED TABLE projects STORE AS projects_table;

To delete all elements from the nested table for a specific employee:

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

To delete a specific project from an employee's list:

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

2. Varray:
Suppose we have a table `DEPARTMENTS` with a varray column for `MANAGERS`.

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

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

CREATE TABLE DEPARTMENTS (
   dept_id NUMBER,
   dept_name VARCHAR2(100),
   managers manager_list
);

Deleting from a varray isn't as straightforward because you can't directly delete an element; instead, you'd typically reset elements or reconstruct the varray:
To "delete" or set to NULL an element at index 3 in the varray:

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

Remove Elements: If you want to effectively remove elements from the varray (which isn't natively supported due to its fixed size), you might have to:
Here's how you could do that:

DECLARE
  v_manager_list manager_list;
BEGIN
  -- Fetch the varray
  SELECT managers INTO v_manager_list
  FROM DEPARTMENTS
  WHERE dept_id = 10;

  -- Let's say we want to remove the manager at index 2
  -- We'll reconstruct the varray without this element
  v_manager_list := manager_list(
    v_manager_list(1), -- Keep the first element
    v_manager_list(3), -- Skip the second, add third
    v_manager_list(4), -- Add the fourth
    v_manager_list(5)  -- Add the fifth
  );
  
  -- Now update the department with this new list
  UPDATE DEPARTMENTS
  SET managers = v_manager_list
  WHERE dept_id = 10;

  COMMIT;
END;
/

Note: When dealing with varrays, remember that their size is fixed at creation, so "removing" an element often involves either setting it to NULL or reconstructing the varray without that element.

A collection is a data structure that acts like a list or a single-dimensional array. Collections are, in fact, the closest you can get in the PL/SQL language to traditional arrays. Through careful analysis you can decide which of the three different types of collection

1. associative array,
2. nested table, and
3. VARRAY

best fits your program's requirements and show you how to define and manipulate those structures. Here are some of the ways I have found collections to be useful. To maintain in-program lists of data. In addition, I use collections to keep track of lists of data elements within my programs. Yes, you could use relational tables or global temporary tables^[1] (which would involve many context switches) or delimited strings, but collections are very efficient structures that can be manipulated with very clean, maintainable code. To improve multirow SQL operations by an order of magnitude or more You can use collections in conjunction with FORALL and BULK COLLECT to dramatically improve the performance of multirow SQL operations. To cache database information Collections are appropriate for caching database information that is static and frequently queried in a single session (or simply queried repeatedly in a single program) to speed up the performance of those queries. I have noticed over the years that relatively few developers know about and use collections. A primary reason for this limited usage is that collections are relatively complicated. Three different types of collections, multiple steps involved in defining and using them, usage in both PL/SQL programs and database objects, more complex syntax than simply working with individual variables.
In the next lesson, you will learn how to delete entire nested tables and varrays.

[1] global temporary tables: In Oracle, a global temporary table (GTT) is a database table that stores data temporarily for the duration of a user session or transaction. The data in a GTT is private to the session that created it and is not visible to other sessions, making them useful for storing intermediate results or temporary data without affecting other users.