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

Found or Not Found, That is The Question. Do You Have The Answer?

Here is a new puzzler from Steven Feuerstein:

Which of the following circumstances does not raise a NO_DATA_FOUND exception? (Hint: There is more than one)

  1. A SELECT INTO statement that finds no rows
  2. A numeric FOR loop whose low value or high value is NULL
  3. An attempt to get the value of an element in a collection whose index is not defined
  4. Reading past the end of a file with UTL_FILE
  5. Reading past the end of a BFILE with DBMS_LOB
  6. A SELECT BULK COLLECT INTO statement that finds no rows
  7. A SELECT COUNT(*) INTO statement that finds no rows
  8. A SELECT my_function FROM dual, where my_function is a function that raises a NO_DATA_FOUND exception.

I have added 6, 7 and 8 to the list above just to make it a little bit more interesting. I’m not going to tell you the answer, but I will give you the following hints:

Now, the first person who posts the correct answer in the comment section below will get an iPhone…just kidding of course, but he or she will get a big round of applause. You can also send your answers to Puzzler@toadworld.com, you may win a t-shirt.


Filed in Oracle, Tips on 14 Sep 07


Reader's Comments

  1. |

    2

  2. |

    Delfino, thanks for your response, however, your answer is not complete. The answer should list all the circumstances that do not raise a NO_DATA_FOUND exception, not just one. I’ll update the post to make the question clearer.

  3. |

    7 is ambiguous Do you mean the WHERE criteria match no rows (in which case you get a count() of zero) or that you have a HAVING clause such that the select returns no rows [eg select count() from table where 1=2 having count(*) != 0] 8 depends on whether the SELECT is a SELECT INTO, SELECT BULK COLLECT INTO, Cursor for loop. I’d go with 2 and 6

  4. |

    2 (raises a VALUE_ERROR), 6, 7, 8

    :-) Patrick

  5. |

    @Gary: You get a round of applause for your excellent thinking :).

    Regarding number 7, select count(*) into v from dual where 1 = 2 will not result in a NO_DATA_FOUND exception, but, if you add to it having 1 = 2 or group by 1 for example you’ll end up with NO_DATA_FOUND.

    Regarding 8, the fact that the function raises a NO_DATA_FOUND exception will not result in a NO_DATA_FOUND exception in the client that is calling the function.

    @Patrick: You also get a round of applause for your correct answer.

    clap clap clap clap clap clap clap clap clap clap