COLLECTION Having NULL Values

COLLECTION Having NULL Values (What is best way to iterate?) and avoiding ORA-06531 Error.

Which Error we get if Collection is not assigned?

Declare
Type numberVarray is VARRAY(5)OF NUMBER;
v_numvarray1 numberVarray;
Begin
if v_numvarray1 IS NULL then
Dbms_output.put_line('v_numvarray1 is null');
End If;
if v_numvarray1(1) IS NULL then
Dbms_output.put_line('The first element of v_numvarray1 is null');
End if;
End;

v_numvarray1 is null
Declare
*
ERROR at line 1:
ORA-06531: Reference to uninitialized collection
ORA-06512: at line 14

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.Ename%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 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
 Names_in Employees_MGR.Names_t;
 BEGIN
 Names_in (1) := ‘Ravi’;
 Names_in (5) := ‘Rajan’;
 Employees_MGR.Show_Names(Names_in);
 END;

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 the 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;

Get involved and leave your Comments in the Box Below. The more people get involved, the more we all benefit. So, leave your thoughts before you leave the page.