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.

Comments

  1. 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. 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
  3. and , perhaps surprisingly for some,

    select ndf into n from dual;

    (EE Release 12.1.0.2.0)

    ReplyDelete
  4. in ANSI SQL no_data_found is not an error ?

    ReplyDelete
  5. 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
  6. DECLARE
    n NUMBER;
    BEGIN
    n := ndf();
    DBMS_OUTPUT.PUT_LINE ('NDF? What NDF?');
    END;
    /

    ReplyDelete
    Replies
    1. 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
  7. 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. 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
  8. 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

Quick Guide to User-Defined Types in Oracle PL/SQL

A Twitter follower recently asked for more information on user-defined types in the PL/SQL language, and I figured the best way to answer is to offer up this blog post. PL/SQL is a strongly-typed language . Before you can work with a variable or constant, it must be declared with a type (yes, PL/SQL also supports lots of implicit conversions from one type to another, but still, everything must be declared with a type). PL/SQL offers a wide array of pre-defined data types , both in the language natively (such as VARCHAR2, PLS_INTEGER, BOOLEAN, etc.) and in a variety of supplied packages (e.g., the NUMBER_TABLE collection type in the DBMS_SQL package). Data types in PL/SQL can be scalars, such as strings and numbers, or composite (consisting of one or more scalars), such as record types, collection types and object types. You can't really declare your own "user-defined" scalars, though you can define subtypes  from those scalars, which can be very helpful from the p

The differences between deterministic and result cache features

 EVERY once in a while, a developer gets in touch with a question like this: I am confused about the exact difference between deterministic and result_cache. Do they have different application use cases? I have used deterministic feature in many functions which retrieve data from some lookup tables. Is it essential to replace these 'deterministic' key words with 'result_cache'?  So I thought I'd write a post about the differences between these two features. But first, let's make sure we all understand what it means for a function to be  deterministic. From Wikipedia : In computer science, a deterministic algorithm is an algorithm which, given a particular input, will always produce the same output, with the underlying machine always passing through the same sequence of states.  Another way of putting this is that a deterministic subprogram (procedure or function) has no side-effects. If you pass a certain set of arguments for the parameters, you will always get

My two favorite APEX 5 features: Regional Display Selector and Cards

We (the over-sized development team for the PL/SQL Challenge - myself and my son, Eli) have been busy creating a new website on top of the PLCH platform (tables and packages): The Oracle Dev Gym! In a few short months (and just a part time involvement by yours truly), we have leveraged Oracle Application Express 5 to create what I think is an elegant, easy-to-use site that our users will absolutely love.  We plan to initially make the Dev Gym available only for current users of PL/SQL Challenge, so we can get feedback from our loyal user base. We will make the necessary adjustments and then offer it for general availability later this year. Anyway, more on that as the date approaches (the date being June 27, the APEX Open Mic Night at Kscope16 , where I will present it to a packed room of APEX experts). What I want to talk about today are two features of APEX that are making me so happy these days: Regional Display Selector and Cards. Regional Display Sel