DELETE method

DELETE is used to remove elements from a collection.

The usage of the DELETE method is described as follows:

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

  The DELETE operation immediately releases the memory allocated to the deleted elements.

• 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 does not take effect.
  • DELETE(m,n) : removes all elements with indices between m and n, inclusive, if both m and n exist and m is less than or equal to n. Otherwise, the DELETE method does not take effect.

For both forms of DELETE, 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.

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 them is restored.

3. All elements are deleted.

The restored elements occupy the same memory as the deleted elements. The print_nt stored procedure outputs the nested table variable after initialization and after each DELETE operation.