DELETE Method in PL/SQL โ€“ Remove Elements from Collections

The DELETE method in PL/SQL is used to remove elements from collections such as nested tables, varrays (partially), or associative arrays. It is overloaded, meaning it has multiple versions that allow flexible deletion of elements โ€” individually or in a range.

What is the DELETE Method in PL/SQL?

The DELETE method is a procedure (not a function) that removes one or more elements from a collection. It can be used with:

  • No parameters โ†’ removes all elements

  • One parameter โ†’ deletes a specific element by index

  • Two parameters โ†’ deletes a range of elements from index1 to index2

This method is particularly useful when you want to make your collection sparse, meaning some indexes are deleted but others still exist.

Syntax of DELETE Method in PL/SQL

collection_name.DELETE;           -- Deletes all elements
collection_name.DELETE(index);    -- Deletes a single element at index
collection_name.DELETE(start, end); -- Deletes a range of elements

Example: Using DELETE to Remove a Range of Elements

DECLARE
   -- Define a nested table type
   TYPE number_table IS TABLE OF INTEGER;

   -- Declare a collection variable
   number_list number_table;

   -- Local procedure to print list contents
   PROCEDURE print_list(list_in number_table) IS
   BEGIN
      FOR i IN list_in.FIRST .. list_in.LAST LOOP
         IF list_in.EXISTS(i) THEN
            DBMS_OUTPUT.PUT_LINE('List [' || list_in(i) || ']');
         END IF;
      END LOOP;
   END;
BEGIN
   -- Initialize the collection with 5 elements
   IF NOT number_list.EXISTS(1) THEN
      number_list := number_table(1, 2, 3, 4, 5);
   END IF;

   -- Print collection before deletion
   DBMS_OUTPUT.PUT_LINE('Nested table before a deletion');
   DBMS_OUTPUT.PUT_LINE('-----------------------------');
   print_list(number_list);

   -- Delete elements from index 2 to 4
   number_list.DELETE(2, 4);

   -- Print collection after deletion
   DBMS_OUTPUT.PUT_LINE(CHR(10) || 'Nested table after a deletion');
   DBMS_OUTPUT.PUT_LINE('-----------------------------');
   print_list(number_list);
END;
/

Output:

Nested table before a deletion
-----------------------------
List [1]
List [2]
List [3]
List [4]
List [5]

Nested table after a deletion
-----------------------------
List [1]
List [5]

What Happens in This Example:

  • A nested table of 5 integers is initialized (1 to 5).

  • The DELETE(2, 4) method removes elements at indexes 2, 3, and 4.

  • After deletion, only elements 1 and 5 remain.

  • This results in a sparse collection โ€” gaps exist between the indexes.

  • The EXISTS method is used to safely check for valid elements before printing.

Types of DELETE Method in PL/SQL

VersionDescriptionExample
DELETEDeletes all elementsnumber_list.DELETE;
DELETE(i)Deletes a single element at inumber_list.DELETE(2);
DELETE(i, j)Deletes a range of elementsnumber_list.DELETE(2, 4);

Dense vs Sparse Collections

  • A dense collection has no gaps between index values.

  • A sparse collection has missing indexes (due to deletions).

  • After using DELETE, your collection may become sparse.

  • Use EXISTS(i) before accessing a deleted index to avoid exceptions.

Best Practices When Using DELETE in PL/SQL

  • Always check with EXISTS(i) before accessing elements.

  • Use FIRST and LAST to determine index bounds in loops.

  • Do not assume indexes are continuous after deletions.

  • Use DELETE to simulate removal of rows from in-memory tables.

๐Ÿ“˜ AmantPoint Exclusive Learning Series
ยฉ 2025 AmantPoint. All rights reserved. Educational content for developers and learners.

Scroll to Top