I execute this statement:
CREATE OR REPLACE FUNCTION ndf RETURN NUMBER IS BEGIN RAISE NO_DATA_FOUND; END; /
We all know what that function is going to do, right? #Fail, as one might say on Twitter.
So the brain teaser is:
In the block below, replace <statement> with a single statement that does not contain any exception handling, but does call the NDF function, so that after the block executes, "NDF? What NDF?" is displayed on the screen.
DECLARE n NUMBER; BEGIN <statement> DBMS_OUTPUT.PUT_LINE ('NDF? What NDF?'); END; /
I will wait a bit to post my answer. I encourage you to post yours as a comment to this blog post.
Wait for it....
After a couple of days of collecting responses on this post and also in LinkedIn, it's time to publish the answer:
You can replace <statement> with any DML statement that executes the function, and the failure of the function with an unhandled NO_DATA_FOUND exception will not cause the SQL statement to terminate with said exception.
Instead, the SQL engine swallows up that exception and simply returns NULL to the statement.
Why, you might wonder, would the SQL engine do this?
NO_DATA_FOUND is, on the one hand, an exception like any other. And on the other hand, it is different, in that the lack of data often does not indicate any kind of actual error, but simply a data condition. And so it was decided that when a function executed within a SQL statement fails with an unhandled NO_DATA_FOUND, that NULL would simply be returned.
You might not like that answer or decision, but there it is.
Now, there is another way to both invoke the NDF function in a single statement and not have the exception terminate the block, as Edwin points out in the comments: Call the function inside a COALESCE function call.
COALESCE offers the very cool feature of not evaluating an expression in its least until it needs to (in contrast, for example, to NVL, which always evaluates the second argument, even if the first argument is not NULL.
Update 12-21: Jeff Kemp (@jeffreykemp) notes on LinkedIn that since the text in the brain teaser says "call the NDF function", COALESCE is not a valid answer, since you never call the function. Strictly speaking he is correct.