Beware of Unhandled Exceptions
Moving functions into the realm of SELECT statements makes handling exceptions a whole new ball game. It’s no longer as simple as causing the function to fail and raising the error to the calling application. For example, how should we handle the situation when the following function raises the NO DATA FOUND exception?
CREATE OR REPLACE FUNCTION unhandled
RETURN number_t AS
v_ret_val number_t := number_t( );
v_dummy NUMBER;
BEGIN
SELECT 1
INTO v_dummy
FROM DUAL
WHERE 1 = 2;
v_ret_val.EXTEND;
v_ret_val(v_ret_val.LAST) := 1;
RETURN(v_ret_val);
END;Should the exception be returned from a SELECT statement like this?
SQL> SELECT *
2 FROM TABLE(unhandled);
COLUMN_VALUE
------------------------
ORA-01403: no data foundThat would require Oracle to keep track of two possible result set structures—one for successful execution and one with a single VARCHAR2 column to hold a potential error message. That might be possible but would wreak havoc with nested table functions, because they too would have to handle two different return structures. That would get far too complicated far too fast.
Perhaps the SELECT should just fail outright?
SQL>SELECT *2FROM TABLE(unhandled);ORA-01403: no data found
That’s better than returning an error message but could be a little confusing.
The answer is that Oracle takes the relatively easy way out by stating that the failing function simply returned no rows.
SQL>SELECT *2FROM TABLE(unhandled);no rows selected
You need to be ...