EXISTS Method in PL/SQL – Check Element Presence Safely

The EXISTS Method in PL/SQL is a built-in function used to determine whether an element exists at a specific subscript (index) in a collection. It is one of the safest and most essential methods when working with nested tables, associative arrays, or sparse collections in Oracle PL/SQL.

What is the EXISTS Method in PL/SQL?

The EXISTS Method in PL/SQL takes a single index (subscript) as input and returns a Boolean (TRUE or FALSE) indicating whether the collection contains an element at that index.

Key Features:

Safe for null collections – does not raise exceptions.
Works with: Nested tables, associative arrays, varrays.
Returns: TRUE if element exists, FALSE otherwise.

Syntax of EXISTS Method in PL/SQL

collection_name.EXISTS(index)

collection_name: Any PL/SQL collection (nested table, associative array, varray)
index: The subscript value (numeric or string for associative arrays)

Example: EXISTS with Nested Table

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

   -- Procedure to print elements
   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
   -- Check if index 1 exists before initialization
   IF NOT number_list.EXISTS(1) THEN
      -- Initialize collection with 5 elements
      number_list := number_table(1, 2, 3, 4, 5);
   END IF;

   DBMS_OUTPUT.PUT_LINE('Nested table before deletion');
   print_list(number_list);

   -- Delete the second element
   number_list.DELETE(2);

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

Output:

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

Nested table after deletion
List [1]
List [3]
List [4]
List [5]

Why Use the EXISTS Method in PL/SQL?

To safely check if an index exists without raising COLLECTION_IS_NULL errors.
To loop through sparse collections created by deletion or partial initialization.
To validate access to associative arrays with dynamic keys.

EXISTS vs. COUNT in PL/SQL

Method	Use Case
`EXISTS(i)`	Check if index `i` is initialized or exists
`COUNT`	Get total number of elements in collection

The EXISTS Method in PL/SQL is the only collection method that will not raise an exception if the collection is NULL.

Use Case: Sparse and Null Collections

The following collections may contain gaps or be uninitialized:

Sparse collections: Elements are deleted or skipped.
Null collections: Never initialized or declared as NULL.

Using EXISTS(index) allows you to safely access these collections without exceptions like COLLECTION_IS_NULL.

Best Practices for EXISTS Method in PL/SQL

Always use EXISTS(index) before reading or updating a collection index.
Combine with FIRST and LAST when looping through elements.
Avoid accessing indexes directly in sparse or null collections without checking.

AmantPoint Exclusive Learning Series
© 2025 AmantPoint. All rights reserved. Educational content for developers and learners.