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.

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.