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?

VALUE_ERROR comes whenever PL/SQL Runtime engine tries to execute a numeric For Loop and either the low and high expression in the Loop’s header evaluates to NULL. So, Raising VALUE_ERROR has nothing to do with your Collection.

It’s clear that to avoid this Exception, you must need to ensure that neither the low nor the high expression evaluates to NULL. There are several ways to avoid this error while working with Collections which we can use by checking their advantages and drawbacks.

Each approach example is an implementation of the DISPLAY_NAMES  Procedure defined in this package specification: 

PACKAGE EMPLOYEE_MGR

IS

TYPE NAMES_T IS TABLE OF EMPLOYEES.LAST_NAME%TYPE INDEX BY PLS_INTEGER;

PROCEDURE DISPLAY_NAMES  (NAMES_IN IN NAMES_T);

END EMPLOYEE_MGR;

Approach 1. Use NVL to ensure that the FOR-Loop header’s low and high expressions never return NULL. 

PROCEDURE DISPLAY_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 DISPLAY_NAMES ;

Approach 2. Execute the loop only if at least one element is defined in the collection. 

PROCEDURE DISPLAY_NAMES  (NAMES_IN IN NAMES_T)

IS

BEGIN

  IF NAME_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 DISPLAY_NAMES  ;

Approach 3. Execute the FOR loop with 1 for the low value and COUNT for the high value. 

PROCEDURE DISPLAY_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 DISPLAY_NAMES ;

Approach 4. Use a WHILE loop and the FIRST and NEXT collection methods. 

PROCEDURE DISPLAY_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 DISPLAY_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. But still, the first approach should never be used and that the other three techniques should be chosen only when certain conditions are met.

When to use Approach 1?

The first approach, using NVL is hard to understand and maintain.

FOR Indx IN NVL (NAMES_IN.FIRST, 0) .. NVL (NAMES_IN.LAST, -1)

Other users have to study this code to determine what exactly the point of using NVL. SO, there is a chance of misinterpretation and then the introduction of a bug. So, instead of using this approach write code that explains itself.

When to use Approach 2?

Second approach, which uses 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 NAME_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;

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.”

But not recommended 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: 

DECLARE

NAMES_IN EMPLOYEE_MGR.NAMES_T;

BEGIN

NAMES_IN (1) := ‘Ravi’;

NAMES_IN(4) := ‘Ankit’;

NAMES_IN(6):=’Rajan’;

EMPLOYEE_MGR.DISPLAY_NAMES (NAMES_IN); 

END;

Above block will throw below Error:

DECLARE

*

ERROR at line 1:

ORA-01403: no data found

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.

This technique useful 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).

When to use Approach 3?

The third approach 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.

But this technique 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.

When to use Approach 4?

Now, 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 code 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 all undefined index values.