NO_DATA_FOUND exception not thrown when used in SELECT INTO

A minimal example is:

CREATE FUNCTION raise_exception RETURN INTISBEGIN  RAISE NO_DATA_FOUND;END;/

If you do:

SELECT raise_exceptionFROM   DUAL;

You will get a single row containing a NULL value - Ask Tom states:

it has ALWAYS been that way

and then followed up with:

no_data_found is not an error - it is an "exceptional condition". You, the programmer, decide if something is an error by catching the exceptional condition and handling it (making it be "not an error") or ignoring it (making it be an error).

in sql, no data found quite simply means "no data found", stop.

Under the covers, SQL is raising back to the client application "hey buddy -- no_data_found". The client in this case says "ah hah, no data found means 'end of data'" and stops.

So the exception is raised in the function and the SQL client sees this and interprets this as there is no data which is a NULL value and "handles" the exception.

So

DECLARE  variable_name VARCHAR2(50);BEGIN  SELECT raise_exception  INTO   variable_name  FROM   DUALEND;/

Will succeed as the DUAL table has a single row and the exception from the function will be handled (silently) and the variable will end up containing a NULL value.

However,

BEGIN  DBMS_OUTPUT.PUT_LINE( raise_exception );END;/

The exception is this time being passed from the function to a PL/SQL scope - which does not handle the error and passes the exception to the exception handler block (which does not exist) so then gets passed up to the application scope and terminates execution of the program.

And Ask Tom states:

Under the covers, PLSQL is raising back to the client application "hey -- no_data_found. The client in this case says "uh-oh, wasn't expecting that from PLSQL -- sql sure, but not PLSQL. Lets print out the text that goes with this exceptional condition and continue on"

You see -- it is all in the way the CLIENT interprets the ORA-xxxxx message. That message, when raised by SQL, is interpreted by the client as "you are done". That message, when raised by PLSQL and not handled by the PLSQL programmer, is on the other hand interpreted as "a bad thing just happened"

Both PLSQL and SQL actually do the same thing here. It is the CLIENT that is deciding to do something different.

Now, if we change the function to raise a different exception:

CREATE OR REPLACE FUNCTION raise_exception RETURN INTISBEGIN  RAISE ZERO_DIVIDE;END;/

Then both:

SELECT raise_exceptionFROM   DUAL;

and:

BEGIN  DBMS_OUTPUT.PUT_LINE( raise_exception );END;/

do not know how to handle the exception and terminate with ORA-01476 divisor is equal to zero.