Oracle PL/SQL for DBAs

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 found

That 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 *
      2    FROM 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 *
      2    FROM TABLE(unhandled);

    no rows selected

You need to be ...