msgbartop
News, views, tips and tricks on Oracle and other fun stuff
msgbarbottom

NO_DATA_FOUND Gotcha

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!


Filed in Oracle, Tips on 10 Apr 07 | Tags: ,


Reader's Comments

  1. |

    Here’s one that has probably bitten a few people.

    The count() function does not raise a no_data_found error when no rows are returned, even when the table is empty.

    SQL>create table js ( x integer);

    Table created.

    SQL> SQL>declare 2 v_x integer; 3 begin 4 select count(*) into v_x from js; 5 dbms_output.put_line(v_x); 6 end; 7 / 0

    PL/SQL procedure successfully completed.

  2. |

    Thanks Jared. I have been bitten with this “gotcha” when I first started with PL/SQL. Then, I learned that a SELECT INTO statement that calls a SQL aggregate function, such as COUNT(*) or AVG(), always returns a value or a null. Aggregate functions are guaranteed to return a single value, even if no “data is found”.

  3. |

    I often do this sort of thing…

    select nvl(max(col), ‘DEFAULT_VALUE’) into l_foo from table_name where x=’BLAH';

    In this case when there is no value I set a default and don’t get the error. In the event that there is more than one value I only get one (I usually only implement this when that is very unlikely).

    • Ethan
  4. |

    Using nvl() ensures that your query will retrieve a non-null value.

    It does not prevent any error condition though, as there would not be on.

    select max(x) from js where js is an empty table will not raise an error.

  5. |

    Actualy there is a way to cause a COUNT() query to raise the NO_DATA_FOUND exception : if you add a group by clause to the query, e.g., SQL> DECLARE 2 l_count number; 3 BEGIN 4 select count() 5 into l_count 6 from all_objects 7 where 1 =0 8 group by 1; 9 DBMS_OUTPUT.put_line (‘RESULT = ‘||l_count); 10 EXCEPTION 11 when NO_DATA_FOUND then 12 DBMS_OUTPUT.put_line (‘Query returned no data!’); 13 END; 14 / Query returned no data!

    PL/SQL procedure successfully completed

  6. |

    Follow Up my own comment : I’d like to note that in case of adding a group by clause to such a query, you should also deal with a possibility of too_many_rows – if the query actualy succeeds….

  7. |

    Raising NO_DATA_FOUND on subscription error. It seems the developers were not busy enough to create an own out_of_bound exception for it

    Karl

  8. |

    Simon, what do you mean by ” too_many_rows – if the query actualy succeeds….”

    I took off the “1 =0″ and it ran with no problem.