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:

• NO_DATA_FOUND Gotcha
• No Data Found: Bug or Feature?
• NO_DATA_FOUND in Functions
• UTL_FILE.GET_LINE
• DBMS_LOB.READ

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.

Share this:

• Share

• Twitter
• Facebook
• LinkedIn
• Email
• StumbleUpon
• Google +1

Related articles:

• NO_DATA_FOUND Gotcha
• DUAL behavior change
• How The Oracle Database Processes SQL Statements

Filed in Oracle, Tips on 14 Sep 07

---

Reader's Comments

1. Delfino | 14 Sep 2007 1:09 pm

   2

2. Eddie Awad | 14 Sep 2007 3:22 pm

   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. Gary | 15 Sep 2007 1:56 pm

   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. Patrick Wolf | 15 Sep 2007 11:37 pm

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

   Patrick

5. Eddie Awad | 16 Sep 2007 6:54 pm

   @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