Now I show what I am talking about.
I created the following simple function
CREATE OR REPLACE FUNCTION test_function RETURN dual.dummy%TYPE IS l_dummy dual.dummy%TYPE; BEGIN SELECT dummy INTO l_dummy FROM dual WHERE 1 = 0; DBMS_OUTPUT.put_line('Finish'); RETURN l_dummy; END test_function;
Implicit cursor in it will never return any row, so NO_DATA_FOUND exception should be raised.
In PL/SQL all works as I expected, the exception will be raised
DECLARE l_dummy dual.dummy%TYPE; BEGIN l_dummy := test_function; END;
But in SQL the following statement works without any exception and returns one row with NULL value
SELECT test_function FROM dual;
What is more, when nothing is returned by the cursor the function is interrupted ('Finish' is not outputed), but any exception is not raised.
How is it explained by Oracle experts? Here I found long discussion about the topic. But to be honest Tom's explanations do not dispel my doubts as well as usual.
According to a quotation from this discussion
I concluded that when I am going to use a function in SQL I handle NO_DATA_FOUND explicitely and raise custom exception (using RAISE_APPLICATION_ERROR) or PROGRAM_ERROR.I went back and forth on this issue - it is a complex 'problem'. Right now the best suggestion would be to always catch when no_data_found when using anything that can raise it - and if it is an ERROR, make it so.The problem with no_data_found is that it is both "an error" and "not an error" depending on 'who you ask', what you are doing. By catching it and being un-ambiguous "this is an ERROR", the problem is resolved.
I changed the TEST_FUNCTION function in this way
CREATE OR REPLACE FUNCTION test_function RETURN dual.dummy%TYPE IS l_dummy dual.dummy%TYPE; BEGIN SELECT dummy INTO l_dummy FROM dual WHERE 1 = 0; DBMS_OUTPUT.put_line('Finish'); RETURN l_dummy; EXCEPTION WHEN NO_DATA_FOUND THEN raise_application_error(-20200, 'No data found'); END test_function;
And now the exception is raised from SQL too.