1

I have this PL/SQL function:

CREATE OR REPLACE FUNCTION Test_FunctionNoDataFound RETURN NUMERIC AS
    var NUMERIC;
BEGIN
    SELECT val INTO var FROM table1 WHERE id = -1; -- Throw NO_DATA_FOUND
    RETURN 1;
END;

And I want to run this SQL statement:

SELECT Test_FunctionNoDataFound FROM dual;

Even if Test_FunctionNoDataFound throw an exception, my statement runs normally and the function returns NULL value.
But now, if Test_FunctionNoDataFound is defined as:

CREATE OR REPLACE FUNCTION Test_FunctionNoDataFound RETURN NUMERIC AS
    var NUMERIC;
BEGIN
    SELECT val INTO var FROM table1 WHERE id > 0; -- Throw TOO_MANY_ROWS
    RETURN 1;
END;

If I run the previous SELECT statement, this time, I get an exception.

Why NO_DATA_FOUND exception has a different behavior than TOO_MANY_ROWS?
Is it the only exception with this kind of behavior?

  • 2
    Tom Kyte explains it asktom.oracle.com/pls/asktom/… – Philᵀᴹ Jun 1 '18 at 12:28
  • Thanks. I already read this post but I don't understand the notion of exceptional condition he is talking about. I didn't find any reference of it in the official documentation. – Pierre Jun 1 '18 at 12:50
  • It's Oracle. Many things are just the way they are because that's the way they are. Don't forget the Oracle DBMS has been around since 1979. That's almost 40 years! – Colin 't Hart Jun 1 '18 at 13:30
  • 1
    I think you need to mentally think of them as "informational messages from the RDBMS that require a course of action" rather than exceptions. – Philᵀᴹ Jun 2 '18 at 11:14
  • 1
    it comes down to the fact that NO_DATA_FOUND is not necessarily an error. Whilst retrieving data we WILL reach the end of our list and NO_DATA_FOUND is just the way that that EOL message is sent. TOO_MANY_ROWS on the other hand is and error condition that the developer should be trapping and handling/raising. The advice from @Philᵀᴹ is probably the best way to change your thinking about it. – BriteSponge Jun 4 '18 at 10:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.