Oracle and PL/SQL Recipes: A Problem-Solution Approach

10-11. Checking Whether an Element Exists

Problem

You are processing elements in a collection but cannot be certain that each element exists. Referencing an element in a collection that does not exist will throw an exception. You want to avoid exceptions by testing for existence before you access an element.

Solution

Use the EXISTS method to test whether a collection has a value for a particular index value. In the following solution, a table collection is created, and then the second element is deleted. It is important to note that a deleted element or an element that was never initialized is not equivalent to an element with a null value.

DECLARE TYPE ctype IS TABLE OF DATE INDEX BY BINARY_INTEGER; cdates ctype; BEGIN    FOR i IN 1..3 ...