Lewis Cunningham’s “mini” tip about the NO_DATA_FOUND exception in PL/SQL is interesting. He shows how this exception is not always raised when you expect it to be raised. Here is another NO_DATA_FOUND gotcha you should be aware of:

  EDDIE@XE> DECLARE
    2     TYPE my_tab_t IS TABLE OF all_objects%ROWTYPE
    3        INDEX BY BINARY_INTEGER;
    4
    5     my_tab my_tab_t;
    6  BEGIN
    7     SELECT *
    8     BULK COLLECT INTO my_tab
    9       FROM all_objects
   10      WHERE ROWNUM <= 10;
   11     DBMS_OUTPUT.put_line ('Woohoo! Query returned no exception.');
   12  EXCEPTION
   13     WHEN NO_DATA_FOUND
   14     THEN
   15        DBMS_OUTPUT.put_line ('No data here my friend!');
   16  END;
   17  /
  Woohoo! Query returned no exception.

The above query returns 10 records. The NO_DATA_FOUND is not raised. Now, let’s change the WHERE clause:

  EDDIE@XE> DECLARE
    2     TYPE my_tab_t IS TABLE OF all_objects%ROWTYPE
    3        INDEX BY BINARY_INTEGER;
    4
    5     my_tab my_tab_t;
    6  BEGIN
    7     SELECT *
    8     BULK COLLECT INTO my_tab
    9       FROM all_objects
   10      WHERE 1 = 2;
   11     DBMS_OUTPUT.put_line ('Woohoo! Query returned no exception.');
   12  EXCEPTION
   13     WHEN NO_DATA_FOUND
   14     THEN
   15        DBMS_OUTPUT.put_line ('No data here my friend!');
   16  END;
   17  /
  Woohoo! Query returned no exception.

Abviously, the query returns no rows. Still, the NO_DATA_FOUND exception is not raised. Huh! Well, this is a documented behavior: Because the processing of the BULK COLLECT INTO clause is similar to a FETCH loop, it does not raise a NO_DATA_FOUND exception if no rows match the query.

Let’s try something else:

  EDDIE@XE> DECLARE
    2     TYPE my_tab_t IS TABLE OF all_objects%ROWTYPE
    3        INDEX BY BINARY_INTEGER;
    4
    5     my_tab my_tab_t;
    6  BEGIN
    7     SELECT *
    8     BULK COLLECT INTO my_tab
    9       FROM all_objects
   10      WHERE 1 = 2;
   11     DBMS_OUTPUT.put_line ('Woohoo! Query returned no exception.');
   12     DBMS_OUTPUT.put_line ('my_tab(1): ' || my_tab (1).object_name);
   13     DBMS_OUTPUT.put_line ('Woohoo! Successfully displayed output.');
   14  EXCEPTION
   15     WHEN NO_DATA_FOUND
   16     THEN
   17        DBMS_OUTPUT.put_line ('No data here my friend!');
   18  END;
   19  /
  Woohoo! Query returned no exception.
  No data here my friend!

As you can see, the NO_DATA_FOUND exception is raised this time. It is raised because my_tab is empty and not because the query returns no rows. This is also a documented behavior: NO_DATA_FOUND is raised when a subscript designates an element that was deleted, or a nonexistent element of an associative array.

In this case, to avoid the exception and to populate the associative array with one empty record, you can do something like this:

  EDDIE@XE> DECLARE
    2     TYPE my_tab_t IS TABLE OF all_objects%ROWTYPE
    3        INDEX BY BINARY_INTEGER;
    4
    5     my_tab my_tab_t;
    6  BEGIN
    7     SELECT *
    8     BULK COLLECT INTO my_tab
    9       FROM all_objects
   10      WHERE 1 = 2;
   11
   12     DBMS_OUTPUT.put_line ('Woohoo! Query returned no exception.');
   13
   14     IF my_tab.COUNT = 0
   15     THEN
   16        my_tab (1).object_name := NULL;
   17     END IF;
   18
   19     DBMS_OUTPUT.put_line ('my_tab(1): ' || my_tab (1).object_name);
   20     DBMS_OUTPUT.put_line ('Woohoo! Successfully displayed output.');
   21  EXCEPTION
   22     WHEN NO_DATA_FOUND
   23     THEN
   24        DBMS_OUTPUT.put_line ('No data here my friend!');
   25  END;
   26  /
  Woohoo! Query returned no exception.
  my_tab(1):
  Woohoo! Successfully displayed output.

Interesting!

---

Possibly related:

• Found or Not Found, That is The Question. Do You Have The Answer?
• Flash CFFORM Gotcha
• ArrayDeleteAt Gotcha
• Little known way to get the error message in PL/SQL
• links for 2006-12-08

---

Tagged gotcha, pl/sql | Post a Comment