As Published In
Oracle Magazine
September/October 2009

TECHNOLOGY: PL/SQL


On Looping, First, and Last

By Steven Feuerstein Oracle ACE Director

Choose the best approach to prevent a VALUE_ERROR exception.

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;

 

 

Next Steps 


READ more PL/SQL Practices
 columns
oracle.com/technetwork/articles/index.html/plsql

 DOWNLOAD Oracle Database 11g

 DISCUSS PL/SQL

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. 


Steven Feuerstein Headshot

Steven Feuerstein (steven.feuerstein@quest.com) is Quest Software’s PL/SQL evangelist. He has published 10 books on Oracle PL/SQL (O’Reilly Media) and is an Oracle ACE Director. More information is available at stevenfeuerstein.com.

 

Send us your comments