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 errorORA-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,

11 comments:

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.

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).

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);

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.