DELETE method

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

The usage of the DELETE method is described as follows:

• The DELETE method can be used to delete all elements (of any type) from a collection.

  The memory allocated to the deleted elements is immediately released.

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

  • DELETE(n) : deletes the element at index n. If the element does not exist, the DELETE operation has no effect.
  • DELETE(m,n) : deletes all elements with indexes between m and n, inclusive, provided that both m and n exist and m is less than or equal to n. Otherwise, the DELETE operation has no effect.

For both forms of DELETE, PL/SQL retains placeholders for the deleted elements. Therefore, the deleted elements are still counted in the internal size of the collection. You can restore the deleted elements by assigning them 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 the deleted elements is restored.

3. All elements are deleted.

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