Oracle EXISTS method.

Preamble

In Oracle PL/SQL, the EXISTS method is a function that tells you if a specified Varray or Nested Tables element exists.

Syntax of the EXISTS method in Oracle PL/SQL

collection_name.EXISTS (n);

Method parameters or arguments

• collection_name – the name of the collection.
• EXISTS (n) – returns TRUE if the n-th element of the collection exists and FALSE otherwise.

Note:

• If n is out of tolerance, EXISTS returns FALSE instead of calling the predefined exception SUBSCRIPT_OUTSIDE_LIMIT.
• For a remote item, EXISTS (n) returns FALSE even if DELETE has saved a place for it.
• See also collection methods: DELETE, TRIM, EXTEND, FIRST and LAST, COUNT, LIMIT, PRIOR and NEXT.

Consider an example to understand how to use the EXISTS collection method in Oracle PL/SQL.

EXISTS example with Nested Tables

Consider an example that initializes Nested Tables with four elements, deletes the second element and prints either the value or the status of the elements from 1 to 6.

DECLARE
TYPE NumList IS TABLE OF INTEGER;
n NumList := NumList(1,3,5,7);
BEGIN
n.DELETE(2); -- Delete the second element

FOR i IN 1...6 LOOP
IF n.EXISTS(i) THEN
DBMS_OUTPUT.PUT_LINE('n(' || i || ') = ' || n(i));
ELSE
DBMS_OUTPUT.PUT_LINE('n(' || i || ') does not exist');
END IF;
END LOOP;
END;

Result:
n(1) = 1
n(2) does not exist
n(3) = 5
n(4) = 7
n(5) does not exist
n(6) does not exist

PL/SQL tutorial: Collection Method EXISTS in Oracle Database

About Enteros

Enteros offers a patented database performance management SaaS platform. It proactively identifies root causes of complex business-impacting database scalability and performance issues across a growing number of clouds, RDBMS, NoSQL, and machine learning database platforms.