DELETE method

The DELETE method is used to remove elements from a collection.

The DELETE method is used as follows:

• The DELETE method can remove all elements (of any type) from a collection.

  The memory allocated to the deleted elements is immediately released.

• For associative arrays or nested tables (excluding variable-length arrays):

  • DELETE(n): removes the element at index n. If the element does not exist, the DELETE method has no effect.
  • DELETE(m,n): removes all elements from index m to n, inclusive, if both m and n exist and m is less than or equal to n. Otherwise, the DELETE method has no effect.

For both forms of the DELETE method, PL/SQL retains placeholders for the deleted elements. Therefore, the deleted elements are included in the collection's internal size, and you can restore them by assigning valid values to them.

Here is an example:

obclient> CREATE OR REPLACE TYPE nested_type IS TABLE OF VARCHAR(20);/
Query OK, 0 rows affected

obclient> CREATE OR REPLACE PROCEDURE print_t (t nested_type) AUTHID DEFINER IS
  i  NUMBER;
BEGIN
  i := t.FIRST;

  IF i IS NULL THEN
    DBMS_OUTPUT.PUT_LINE('t is empty');
  ELSE
    WHILE i IS NOT NULL LOOP
      DBMS_OUTPUT.PUT('t.(' || i || ') = ');
      DBMS_OUTPUT.PUT_LINE(NVL(TO_CHAR(t(i)), 'NULL'));
      i := t.NEXT(i);
    END LOOP;
  END IF;

  DBMS_OUTPUT.PUT_LINE('---');
END print_t;
/
Query OK, 0 rows affected

obclient> DECLARE
  t nested_type:= nested_type('A', 'B', 'C', 'D', 'E', 'F');
BEGIN
  print_t(t);

  t.DELETE(3);     -- Deletes the third element
  print_t(t);

  t(3) := 'cc';    -- Restores the third element
  print_t(t);

  t.DELETE(2, 4);  -- Deletes a sequence of elements
  print_t(t);

  t(3) := 'cccc';    -- Restores the third element
  print_t(t);

  t.DELETE;        -- Deletes all elements
  print_t(t);
END;
/
Query OK, 0 rows affected

t.(1) = A
t.(2) = B
t.(3) = C
t.(4) = D
t.(5) = E
t.(6) = F
---
t.(1) = A
t.(2) = B
t.(4) = D
t.(5) = E
t.(6) = F
---
t.(1) = A
t.(2) = B
t.(3) = cc
t.(4) = D
t.(5) = E
t.(6) = F
---
t.(1) = A
t.(5) = E
t.(6) = F
---
t.(1) = A
t.(3) = cccc
t.(5) = E
t.(6) = F
---
t is empty
---

In this example, a nested table variable is declared and initialized with six elements. The following operations are performed:

1. The third element is deleted and then restored.

2. A sequence of elements is deleted, and one of the deleted elements is restored.

3. All elements are deleted.

The memory occupied by the restored elements is the same as that of the deleted elements. The print_nt stored procedure outputs the nested table variable after initialization and after each DELETE operation.