Sunday, February 10, 2008

WREAK_APPLICATION_HAVOC

Tom Kyte recently blogged about the senseless and yet strangely common practice of coding something like this:

WHEN OTHERS THEN
   RAISE_APPLICATION_ERROR(-20001,'Following Error Occured:' || SQLERRM);

which fairly obviously achieves nothing except take a standard message like this:

ORA-06501: PL/SQL: program error
ORA-06512: at line 6

and pointlessly scramble it into this:

ORA-20001: Following Error Occured:ORA-06501: PL/SQL: program error
ORA-06512: at line 11

which adds some meaningless text, hides the original line number, and miss-spells "occurred". Not bad for a day's work.

It turned out that some people had been doing this for years because they were simply too stupid to realise that they didn't have to.

Anyway you know all this because you read Tom Kyte's blog. But have a look at this helpful page of advice from Tech On The Net, under "Oracle/PLSQL: SQLERRM Function"

You could use the SQLERRM function to raise an error as follows:

EXCEPTION
   WHEN OTHERS THEN
      raise_application_error
      (-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END;

Or you could log the error to a table as follows:

EXCEPTION
   WHEN OTHERS THEN
      err_code := SQLCODE;
      err_msg := substr(SQLERRM, 1, 200);

      INSERT INTO audit_table (error_number, error_message)
      VALUES (err_code, err_msg);
END;

Now that is even better. The first example adds some meaningless text, hides the original line number, and duplicates the error code (unless it's a NO_DATA_FOUND exception, but let's not go there), to produce something like this:

ORA-20001: An error was encountered - -6501 -ERROR- ORA-06501: PL/SQL: program error
ORA-06512: at line 11

The error logging example pointlessly captures SQLCODE (nobody will ever use it), throws away all but the first 200 characters of the error stack, logs nothing about what happened, and fails to re-raise the exception so if you don't check the log you won't know anything went wrong until your customers start asking where their stuff is.

Wouldn't it be great if there were, say, a Boolean third parameter to RAISE_APPLICATION_ERROR that would make it retain the existing error stack, freeing up the message line for you to put something intelligent and helpful, like, I don't know,

BEGIN
    RAISE program_error;
EXCEPTION
    WHEN OTHERS THEN
        RAISE_APPLICATION_ERROR
        ( -20001
        , 'Biscuits cannot be ordered on a ' || TO_CHAR(SYSDATE,'fmDay') ||
          ' without a hot beverage'
        , TRUE);
END;

to produce something like this:

ORA-20001: Biscuits cannot be ordered on a Sunday without a hot beverage
ORA-06512: at line 5
ORA-06501: PL/SQL: program error

We can but dream.

11 comments:

Niall said...

Hey I really like the error logging example. Particularly the lack of a datestamp and the results if the original routine fails because of an out of space error in the tablespace in which the audit table is kept.

Jeffrey Kemp said...

What's even worse is the "WHEN OTHERS THEN NULL" approach to bug fixing:

1. Defect: "error xxx happens at line #yyy."
2. Fix: BEGIN ... EXCEPTION WHEN OTHERS THEN NULL; END;
3. "Hey, I fixed 10 defects today! How easy was that!"

I undid no less than four instances of this just yesterday.

Nilesh Jethwa said...

error logging is neat and should be complimented with few output statements.

Dashboards

William Robertson said...

Wise words, Nilesh. Error-related compliments should be kept to a minimum, while ads for some kind of crappy half-arsed charting application need to be maximised. Good luck with that.

Szilard Barany said...

Wouldn't it be great if there were, say, a Boolean third parameter...
Tom Kyte told at the last UK*OUG that the developers of PL/SQL asked him what new feature he would like to see in 11g (alas, I can't remember what was his choice).
Is there a "wish list" for the public? I would support your proposal. (I personally would like to see PRAGMA OBSOLETE and an ASSERT implementation).

William Robertson said...

The campaign starts here. Maybe if enough of us lobby for this Oracle 7.1 feature it will get implemented 15 years ago.

Adrian Christie said...

I'd settle for a BOOLEAN data type before we star requesting BOOLEAN parameters.

Kim Berg Hansen said...

For a wish list for PL/SQL improvements, try this:

http://www.iloveplsqland.net

Steven Feuerstein and Bryn Llewellyn initiative to hear "requests for enhancements" from the PL/SQL community.

Anonymous said...

It's been about 10 years since I worked with it, so the memory is a bit hazy, but I vaguely recall that dbms_utility.format_error_stack() will put the Oracle error stack into a single string. Declare a large enough varchar2 to hold the return value and you can collect the whole error stack. Append that to your application-specific message... raise_application_error (-20001, a very long string);

William Robertson said...

Wouldn't that be less informative than

RAISE_APPLICATION_ERROR
( -20000
, 'Failed to shut down reactor core'
, TRUE );

Anonymous said...

We know we can raise an app err to produce a custom msg and to output the original sqlerrm, but we want the line number of the original err also, which evidently is produced by setting the third param to TRUE; this only lets us see the ora error, but not the line it happened on. So for the below example, I want to see a stacktrace that lists line 7 in addition to the ora 942 message.

SQL> ed
Wrote file afiedt.buf

1 declare
2 rec_id number := 1729;
3 x number := -1;
4 y number := -1;
5 BEGIN
6 execute immediate 'select 9from dual' into x;
7 execute immediate 'select 9 from duald' into y;
8 EXCEPTION
9 WHEN OTHERS THEN
10 RAISE_APPLICATION_ERROR
11 ( -20117, 'custom err message - rec_id was: ' || rec_id, true);
12* END;
SQL> /
declare
*
ERROR at line 1:
ORA-20117: custom err message - rec_id was: 1729
ORA-06512: at line 10
ORA-00942: table or view does not exist