NO_DATA_FOUND Exceptions in PL/SQL Functions Called in Queries

Functions in PL/SQL can be a tricky thing as they don't always raise an exception when one occurs. You may be asking yourself: How, I thought functions and procedures always raise exceptions when one occurs? This is true except in some very specific situations.

First lets look at a very simple function which is then executed in both PL/SQL and in a query. It should (and does) raise an exception in both cases since we have a 1/0 in it.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27

SQL> CREATE OR REPLACE FUNCTION f_generate_error RETURN VARCHAR2   2  AS   3    l_x pls_integer;   4  BEGIN   5    SELECT 1 INTO l_x FROM dual WHERE 1/0 = 0;   6    RETURN 'No exception raised';   7  END;   8  /   Function created.   SQL> EXEC dbms_output.put_line(f_generate_error); BEGIN dbms_output.put_line(f_generate_error); END;   * ERROR at line 1: ORA-01476: divisor is equal to zero ORA-06512: at "MARTIN.F_GENERATE_ERROR", line 5 ORA-06512: at line 1     SQL> SELECT f_generate_error FROM dual; SELECT f_generate_error FROM dual        * ERROR at line 1: ORA-01476: divisor is equal to zero ORA-06512: at "MARTIN.F_GENERATE_ERROR", line 5

What if we change the query slightly so that the SELECT INTO statement doesn't return any rows (by applying a 1=0 predicate) and raises a NO_DATA_FOUND exception?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27

SQL> CREATE OR REPLACE FUNCTION f_generate_error RETURN VARCHAR2   2  AS   3    l_x pls_integer;   4  BEGIN   5    SELECT 1 INTO l_x FROM dual WHERE 1=0;   6    RETURN 'No exception raised';   7  END;   8  /   Function created.   SQL> EXEC dbms_output.put_line(f_generate_error); BEGIN dbms_output.put_line(f_generate_error); END;   * ERROR at line 1: ORA-01403: no data found ORA-06512: at "MARTIN.F_GENERATE_ERROR", line 5 ORA-06512: at line 1     SQL> SELECT f_generate_error FROM dual;   F_GENERATE_ERROR ----------------------------------------------------------------   1 row selected.

When we run it in PL/SQL it raises an exception but when we run it in a query it doesn't. It just returns a null value which doesn't really tell the calling query that an exception was raised. This can obviously cause issues and unexpected behavior in your application.

According to the Oracle documentation the NO_DATA_FOUND exception will not propagate the exception if run in a query: "Because this exception is used internally by some SQL functions to signal that they are finished, you should not rely on this exception being propagated if you raise it within a function that is called as part of a query."

To get around this issue you can raise a custom exception when you encounter a NO_DATA_FOUND exception in functions so that it will propagate when called via a SQL query. In the example below a custom exception is raised and the exception is propagated when called from a query.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

SQL> CREATE OR REPLACE FUNCTION f_generate_error RETURN VARCHAR2   2  AS   3    l_x pls_integer;   4  BEGIN   5    SELECT 1 INTO l_x FROM dual WHERE 1=0;   6    RETURN 'No exception raised';   7  EXCEPTION   8    WHEN NO_DATA_FOUND THEN   9      raise_application_error(-20001, 'Custom NO_DATA_FOUND');  10  END;  11  /   Function created.   SQL> SELECT f_generate_error FROM dual; SELECT f_generate_error FROM dual        * ERROR at line 1: ORA-20001: Custom NO_DATA_FOUND ORA-06512: at "MARTIN.F_GENERATE_ERROR", line 9