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
and5
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
Version | Description | Example |
---|---|---|
DELETE | Deletes all elements | number_list.DELETE; |
DELETE(i) | Deletes a single element at i | number_list.DELETE(2); |
DELETE(i, j) | Deletes a range of elements | number_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
andLAST
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.