Skip to main content

PL/SQL Brain Teaser: When is NO_DATA_FOUND not?

Here goes:

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....
Wait....
Wait for it....

OK!

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.
Labels:

Comments

  1. Joaquin GonzalezDecember 20, 2016 at 4:38 AM

    Hi Steven,

    How about this?

    DECLARE
    n NUMBER;
    BEGIN
    select ndf into n from dual;
    DBMS_OUTPUT.PUT_LINE ('NDF? What NDF?');
    END;
    /

    NDF? What NDF?

    PL/SQL procedure successfully completed.

    ReplyDelete
  2. Kim Berg HansenDecember 20, 2016 at 4:44 AM

    select ndf into n from dual;

    ReplyDelete
  3. PPDecember 20, 2016 at 4:47 AM

    Would you accept this :

    DECLARE
    n NUMBER;
    BEGIN
    select count(1) into n from dual where ndf() = 1;
    DBMS_OUTPUT.PUT_LINE ('NDF? What NDF?');
    END;
    /

    ReplyDelete
  4. Edwin van MeerendonkDecember 20, 2016 at 5:01 AM

    At least this one

    n := coalesce(1, ndf);

    ReplyDelete
  5. Edwin van MeerendonkDecember 20, 2016 at 5:05 AM

    and , perhaps surprisingly for some,

    select ndf into n from dual;

    (EE Release 12.1.0.2.0)

    ReplyDelete
  6. UnknownDecember 20, 2016 at 6:50 AM

    in ANSI SQL no_data_found is not an error ?

    ReplyDelete
  7. ruudvanDecember 20, 2016 at 12:31 PM

    My first thought before reading the comments was to look for a way to short circuit the function call just like Edwin's first answer.

    It was indeed surprising for me, that the column list in a sql select-into doesn't raise NO_DATA_FOUND. I guess, the more you know.

    Any explanation on why it implicitly assumes a null instead of raising the error, or am I failing to notice something really obvious?

    I already spot checked other sql and plsql exceptions like INVALID_CURSOR, DUP_VAL_ON_INDEX, too_many_rows, etc. and it does raise all of them. (makes sense for too_many_rows)

    ReplyDelete
  8. SwarnapriyaDecember 20, 2016 at 9:13 PM

    DECLARE
    n NUMBER;
    BEGIN
    n := ndf();
    DBMS_OUTPUT.PUT_LINE ('NDF? What NDF?');
    END;
    /

    ReplyDelete
    Replies
    1. SwarnapriyaDecember 21, 2016 at 5:04 AM

      Sorry. The above code will throw an exception.
      The code that could possibly work is:
      DECLARE
      n NUMBER;
      BEGIN
      select nvl(ndf(),1) into n from dual;
      DBMS_OUTPUT.PUT_LINE ('NDF? What NDF?');
      END;

      Delete
  9. UnknownDecember 21, 2016 at 12:01 AM

    Oracle documentation says:

    NO_DATA_FOUND 01403 +100

    Because this exception is used internally by some SQL functions to signal completion, you must not rely on this exception being propagated if you raise it within a function that is invoked as part of a query.

    ReplyDelete
    Replies
    1. Steven FeuersteinDecember 21, 2016 at 5:54 AM

      Exactly. 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 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.

      Delete
  10. Steven FeuersteinDecember 21, 2016 at 5:53 AM

    Thanks, everyone! Excellent responses - especially that reminder about Coalesce. Edwin, I hadn't even thought of that!

    ReplyDelete

Post a Comment

Popular posts from this blog

Why DBMS_OUTPUT.PUT_LINE should not be in your application code

A database developer recently came across my  Bulletproof PL/SQL  presentation, which includes this slide. That first item in the list caught his attention: Never put calls to DBMS_OUTPUT.PUT_LINE in your application code. So he sent me an email asking why I would say that. Well, I suppose that is the problem with publishing slide decks. All the explanatory verbiage is missing. I suppose maybe I should do a video. :-) But in the meantime, allow me to explain. First, what does DBMS_OUTPUT.PUT_LINE do? It writes text out to a buffer, and when your current PL/SQL block terminates, the buffer is displayed on your screen. [Note: there can be more to it than that. For example, you could in your own code call DBMS_OUTPUT.GET_LINE(S) to get the contents of the buffer and do something with it, but I will keep things simple right now.] Second, if I am telling you not to use this built-in, how could text from your program be displayed on your screen? Not without a lot o...
4 comments
Read more

How to Pick the Limit for BULK COLLECT

This question rolled into my In Box today: In the case of using the LIMIT clause of BULK COLLECT, how do we decide what value to use for the limit? First I give the quick answer, then I provide support for that answer Quick Answer Start with 100. That's the default (and only) setting for cursor FOR loop optimizations. It offers a sweet spot of improved performance over row-by-row and not-too-much PGA memory consumption. Test to see if that's fast enough (likely will be for many cases). If not, try higher values until you reach the performance level you need - and you are not consuming too much PGA memory.  Don't hard-code the limit value: make it a parameter to your subprogram or a constant in a package specification. Don't put anything in the collection you don't need. [from Giulio Dottorini] Remember: each session that runs this code will use that amount of memory. Background When you use BULK COLLECT, you retrieve more than row with each fetch, ...
4 comments
Read more

Recommendations for unit testing PL/SQL programs

I have recently received a couple of requests for recommendations regarding unit testing of PL/SQL programs. I thought I would share with you what I told them. First, some background:  unit testing  refers to the process of testing individual subprograms for correctness, as opposed to overall application testing (which, these days, almost always means visiting a website). The basic idea behind unit testing is that if you verify that each individual subprogram works correctly, then you are much less likely to have bugs in higher-level programs that call those tested subprograms. And when you do, you know you can focus on the way the tested subprograms are used, and not the subprograms themselves. The most important application of a unit test is to participate in a regression test , which can be run to verify one's code works today as well as it did yesterday. That will greatly reduce the chance of you upgrading the application and users complaining that a bunch of feature...
38 comments
Read more