On Looping, First, and Last
Choose the best approach to prevent a VALUE_ERROR exception.
By Steven Feuerstein
September/October 2009
If I try to use a FOR loop to iterate from FIRST to LAST and my collection is empty, PL/SQL raises a VALUE_ERROR exception. What’s the best way to avoid raising this error?
First, the raising of VALUE_ERROR has nothing to do with your collection. The PL/SQL runtime engine will raise a VALUE_ERROR exception whenever it tries to execute a numeric FOR loop and either the low or high expression in the loop’s header evaluates to NULL.
To avoid this exception, you must ensure that neither the low nor the high expression evaluates to NULL. When you’re working with collections, there are several ways to accomplish this, most of which should be avoided because of their drawbacks. I will first show you each of them and then offer my views on which should be used and which should be avoided.
Each approach example is an implementation of the show_names procedure defined in this package specification:
PACKAGE employees_mgr
IS
TYPE names_t IS TABLE OF
employees.last_name%TYPE
INDEX BY PLS_INTEGER;
PROCEDURE show_names
(names_in IN names_t);
END employees_mgr;
Approach 1. Use NVL to ensure that the FOR loop header’s low and high expressions never return NULL.
PROCEDURE show_names
(names_in IN names_t)
IS
BEGIN
FOR indx IN NVL (names_in.FIRST, 0)
.. NVL (names_in.LAST, -1)
LOOP
DBMS_OUTPUT.PUT_LINE
(names_in(indx));
END LOOP;
END show_names;
Approach 2. Execute the loop only if at least one element is defined in the collection.
PROCEDURE show_names
(names_in IN names_t)
IS
BEGIN
IF names_in.COUNT > 0
THEN
FOR indx IN
names_in.FIRST .. names_in.LAST
LOOP
DBMS_OUTPUT.PUT_LINE
(names_in(indx));
END LOOP;
END IF;
END show_names;
Approach 3. Execute the FOR loop with 1 for the low value and COUNT for the high value.
PROCEDURE show_names
names_in IN names_t)
IS
BEGIN
FOR indx IN
1 .. names_in.COUNT
LOOP
DBMS_OUTPUT.PUT_LINE
(names_in(indx));
END LOOP;
END show_names;
Approach 4. Use a WHILE loop and the FIRST and NEXT collection methods.
PROCEDURE show_names
(names_in IN names_t)
IS
l_index PLS_INTEGER;
BEGIN
l_index := names_in.FIRST;
WHILE (l_index IS NOT NULL)
LOOP
DBMS_OUTPUT.PUT_LINE
(names_in(l_index));
l_index := names_in.NEXT
(l_index);
END LOOP;
END show_names;
All four approaches achieve the desired effect: VALUE_ERROR will not be raised, even if the number of elements in the names_in collection is 0. Yet I will argue that the first approach should never be used and that the other three techniques should be chosen only when certain conditions are met.
The first approach, using NVL, is a classic example of a programmer’s trying to be too clever by half and ending up with code that is hard to understand and maintain.
Consider the header of the FOR loop:
FOR indx IN NVL (names_in.FIRST, 0)
.. NVL (names_in.LAST, -1)
If I had not written this block originally and now had to maintain it, I would have to study this code to determine what exactly the point of it is. Whenever a developer must analyze and interpret code to uncover its intention, there is a chance of mis interpretation and then the introduction of a bug.
I suggest that, as a general rule, developers avoid being clever and instead write code that explains itself. Which brings me to the second approach: use the COUNT method to ensure that the FOR loop is executed only when there is something in the collection. Here is the relevant code:
IF names_in.COUNT > 0 THEN FOR indx IN names_in.FIRST .. names_in.LAST
I believe this code speaks for itself. It says: “If the collection contains at least one element, iterate from the lowest to the highest index value and take the specified action. If the collection is empty, skip the FOR loop entirely.”
This is a vast improvement over the first approach, yet I cannot recommend it under all circumstances. The problem is that if the actual collection passed to the names_in parameter is sparse (that is, at least one index value between FIRST and LAST is not defined), the FOR loop will raise a NO_DATA_FOUND exception:
SQL> DECLARE 2 names_in employees_mgr.names_t; 3 BEGIN 4 names_in (1) := ‘Kirk’; 5 names_in (5) := ‘Spock’; 6 employees_mgr.show_names(names_in); 7 END; 8 / DECLARE * ERROR at line 1: ORA-01403: no data found
This happens because the FOR loop is instructed to display the name found in names_in(1) through names_in(5). When the PL/SQL runtime engine tries to read the contents of names_in(2), it finds that there is no element defined at index value 2 and it raises NO_DATA_FOUND.
Thus, I recommend this second technique only when you know without any doubt that the collection through which you are iterating is either empty or densely filled (all index values between FIRST and LAST are defined). You can be sure of this whenever you populate the collection with a BULK COLLECT query or with the result of a nested table MULTISET operation (UNION, UNION ALL, INTERSECT, or EXCEPT).
The third technique iterates from 1 to the COUNT of elements in the collection:
FOR indx IN 1 .. names_in.COUNT
This technique has the advantage of avoiding the clutter of an IF statement to ensure that the FOR loop executes only when the collection is not empty. If the collection is empty, COUNT will return 0 (not NULL) and the FOR loop body will not execute, because 0 is less than 1.
It is concise and readable, but it has the same drawback as the previous technique: it assumes that the collection is either empty or densely filled. It also assumes that the lowest defined index value is 1. If you are not absolutely certain that the collection will always be filled, starting with index value 1, you should not use this technique.
Which brings us to the fourth and last approach: don’t use a FOR loop at all. Instead use a WHILE loop and the NEXT method:
l_index := names_in.FIRST;
WHILE (l_index IS NOT NULL)
LOOP
DBMS_OUTPUT.PUT_LINE
(names_in(l_index));
l_index := names_in.NEXT
(l_index);
END LOOP;
This approach makes no assumptions about the contents of the collection. The names_in collection can be empty, densely filled, or sparse, and the program will still “do the right thing.” The key to this technique’s flexibility is the use of the NEXT method. This method returns the next (highest) index value after the specified index value that is defined , ignoring (or, at least conceptually, skipping over) all undefined index values.
You might then expect that I would recommend that you always use this technique if you want to iterate through all the elements of a collection. Yet that is not the case.
Suppose that when I wrote the show_names procedure, it was intended to be used to display the contents of a collection that was populated with a BULK COLLECT statement. In such a case, the collection is always empty or sequentially filled, starting from index value 1. The show_names procedure works properly and the code goes into production.
Now suppose further that a year later, another developer is instructed to make a change to one of the programs that calls employees_mgr.show_names. The developer makes a mistake and deletes several of the elements in the collection that is passed to show_names. The collection is now sparsely filled, but it should not be. Still, show_names does its job without raising any errors. The net result is that show_names has, in effect, covered up an error.
The bottom line is that if you are writing code to iterate through a collection and you know for certain that this collection should be sequentially filled, you should use
FOR indx IN 1 .. <your_collection>.COUNT
if you also know that the collection is always filled from index value 1 (as with BULK COLLECT and MULTISET), or use
IF <your_collection>.COUNT > 0
THEN
FOR indx IN
<your_collection>.FIRST ..
<your_collection>.LAST
if the lowest index value might be a value other than 1.
Next StepsREAD more PL/SQL Practices
columns
oracle.com/technetwork/articles/index.html/plsql
DOWNLOAD Oracle Database 11g
DISCUSS PL/SQL
DISCLAIMER: We've captured these popular historical magazine articles for your reference. Links etc contained within these article possibly won't work. These articles are provided as-is with no guarantee that the information within them is the best advice for current versions of the Oracle Database.