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)
- A SELECT INTO statement that finds no rows
- A numeric FOR loop whose low value or high value is NULL
- An attempt to get the value of an element in a collection whose index is not defined
- Reading past the end of a file with UTL_FILE
- Reading past the end of a BFILE with DBMS_LOB
- A SELECT BULK COLLECT INTO statement that finds no rows
- A SELECT COUNT(*) INTO statement that finds no rows
- 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.
Possibly related:
- OraQA Update
- Little known way to get the error message in PL/SQL
- Bart’s Punishment For Asking Dumb Questions
- You Don’t Have to Use an Aggregator to Keep Track of Your Favorite Topic
- Go ahead, turn your FIPS flagging on
Tagged | Post a Comment
Hope everything is working out...
2
September 14th, 2007, at 1:09 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.
September 14th, 2007, at 3:22 pm #7 is ambiguous
September 15th, 2007, at 1:56 pm #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
2 (raises a VALUE_ERROR), 6, 7, 8
Patrick
September 15th, 2007, at 11:37 pm #@Gary: You get a round of applause for your excellent thinking :).
Regarding number 7,
select count(*) into v from dual where 1 = 2will not result in a NO_DATA_FOUND exception, but, if you add to ithaving 1 = 2orgroup by 1for 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
September 16th, 2007, at 6:54 pm #