WREAK_APPLICATION_HAVOC ....oracle

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.