Skip to Main Content
Oracle PL/SQL for DBAs
book

Oracle PL/SQL for DBAs

by Arup Nanda, Steven Feuerstein
October 2005
Intermediate to advanced content levelIntermediate to advanced
454 pages
14h 44m
English
O'Reilly Media, Inc.
Content preview from 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 ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Oracle PL/SQL Best Practices

Oracle PL/SQL Best Practices

Steven Feuerstein
Expert Oracle PL/SQL

Expert Oracle PL/SQL

Ron Hardman, Michael McLaughlin
Oracle PL/SQL For Dummies

Oracle PL/SQL For Dummies

Michael Rosenblum, Paul Dorsey

Publisher Resources

ISBN: 0596005873Supplemental ContentErrata Page