You can use the EXISTS function to check whether a specified element exists in a variable array or nested table.
EXISTS(n) returns TRUE if the nth element exists in the collection, and returns FALSE if otherwise. If n exceeds the valid range, EXISTS returns FALSE without causing the predefined SUBSCRIPT_OUTSIDE_LIMIT exception.
EXISTS(n) returns FALSE for deleted elements, even if the DELETE method retains placeholders for them.
Example:
obclient> DECLARE
TYPE emp_id IS TABLE OF NUMBER;
n EMP_ID := emp_id(2,4,6,8);
BEGIN
n.DELETE(2); -- Delete the second element.
n.TRIM; -- Trim the last element.
FOR i IN 1..5 LOOP
IF n.EXISTS(i) THEN
DBMS_OUTPUT.PUT_LINE('n(' || i || ') = ' || n(i));
ELSE
DBMS_OUTPUT.PUT_LINE('n(' || i || ') has no value');
END IF;
END LOOP;
END;
/
Query OK, 0 rows affected
n(1) = 2
n(2) has no value
n(3) = 6
n(4) has no value
n(5) has no value
The preceding example initializes and assigns values to four elements in a nested table, deletes the second element, trims the last element, and outputs the values or states of elements 1 to 6.
