when others then exception handling

Question

Background:

I've used a few Oracle articles to develop an error package, which consists of five procedures.

Two of these are Log_And_Return and Log_And_Continue. They are called throughout the program. Each takes input and passes it to the Handle procedure. For example:

PROCEDURE Log_And_Return (error_name)
IS
BEGIN
    Handle (error_name, TRUE, TRUE);
END Log_And_Return; 

The Handle procedure then calls the Log procedure and the Raise_To_Application procedure depending on the variables passed to it, like so:

PROCEDURE Handle (error_name, log_error, reraise_error)    
IS
BEGIN
    // Code to fetch error code and message using error_name input parameter.
    IF log_error THEN
        LOG (error_code, error_message);
    END IF;

    IF in_reraise_error THEN
        Raise_To_Application (error_code, error_message);
    END IF;    
END Handle;

The log procedure stores the date, stacktrace, error code, error message and id and finally the Raise_To_Application procedure does what is says:

raise_application_error (error_code, error_message);

Problem:

My problem is this. Let's say I have a procedure, which performs a query, e.g. fetching a customer record. If this query fails, it's a big problem. So I could do this:

BEGIN    
    SELECT *something*
    FROM *some table*
    WHERE *some field* = *some user input*

    // more logic

EXCEPTION
WHEN NO_DATA_FOUND THEN
    ERR.Log_And_Return('unknown_id');  
WHEN OTHERS THEN
    ERR.Log_And_Return('unknown_error');  
END;

Here, my Log_And_Return procedure takes the input, goes off to a table and returns a string to display to the user. I've a specific error for if the query doesn't find the user's record and a generic error for an unknown error. In both cases, logging is performed which takes the full stacktrace of the error.

However, in my example I've got a "// more logic" section. Let's say, I amend the code to this:

BEGIN    
    SELECT *something* INTO *some variable*
    FROM *some table*
    WHERE *some field* = *user id*

    Call_Another_Procedure(*user id*, *some variable*)

EXCEPTION
WHEN NO_DATA_FOUND THEN
    ERR.Log_And_Return('unknown_id');  
WHEN OTHERS THEN
    ERR.Log_And_Return('unknown_error');  
END;

Now, after the select query, I'm calling another procedure with the result of the select query. Inside this new query, I'm doing a few things, including an update statement, like so:

// bunch of logic

BEGIN
    UPDATE *another table*
    SET *some field* = *some value*
    WHERE *some field* = *variable passed into method*
EXCEPTION
WHEN NO_DATA_FOUND THEN
    Err.Log_And_Return('some_error')
END;

Question:

My problem here is that I throw the NO_DATA_FOUND error if the query returns no results, I log the problem and I then raise an application error in my "Raise_To_Application" procedure... which will then be caught by the "when others" clause in the parent procedure, which will return the wrong message to the user.

What is the workaround to this? Note: If more code needs to be posted, just let me know.

Edit:

One workaround I had considered, and I've no idea if this is recommended or not, would be to wrap every stored procedure with a BEGIN END EXCEPTION block, where every procedure had a "When Others" block that just logged and reraised the most recent error (i.e. using SQLCODE). Then, in my application layer I could specify that if the error is between -20000 and -20999, show it along with its message, otherwise show a generic message (and the DBA can find out what happened in the database by looking at the log table, along with a full stacktrace). Any thoughts on this?

Edit 2:

If anything doesn't make sense, I can clarify. I've heavily changed and simplifier the code to remove things like id parameters and a few other things.

Solution 1

This is pretty much the approach I've been using, since I want to log every entry and exit point in my code:

application_error EXCEPTION;
PRAGMA EXCEPTION_INIT (application_error, -20000);

BEGIN    
    SELECT *something* INTO *some variable*
    FROM *some table*
    WHERE *some field* = *user id*

    Call_Another_Procedure(*user id*, *some variable*)

EXCEPTION
WHEN NO_DATA_FOUND THEN
    ERR.Log_And_Return('unknown_id');
WHEN application_error THEN -- ordinary exception raised by a subprocedure
    ERR.Log_And_Return('application_error');
    RAISE;
WHEN OTHERS THEN
    ERR.Log_And_Return('unknown_error');
    RAISE;
END;

And for subprocedures:

BEGIN
    UPDATE *another table*
    SET *some field* = *some value*
    WHERE *some field* = *variable passed into method*
EXCEPTION
WHEN NO_DATA_FOUND THEN
    Err.Log_And_Return('some_error');  -- this raises ORA-20000
END;

Solution 2

For the when_others exceptions consider of using AFTER SERVERERROR triggers. Something like bellow

create or replace trigger TRG_SERVERERROR 
   after servererror on database
declare
   <some_variable_for_logging_the_call_stack>
begin
   ERR.Log;
end;

I will quote from Tom Kytes when he was permitted to submit three requests for new features in PL/SQL and this is what he say

I jumped at the chance. My first suggestion was simply, “Remove the WHEN OTHERS clause from the language.”

You can also read the following article from Tom Kyte - Why You Really Want to Let Exceptions Propagate

UPD: The whole workflow for the solution in your case is the following(in my subjective opinion)

I'll sugges to Include no WHEN OTHERS. I prefer to receive unfriendly error message, instead of the seamless message - something like "Ooops, something goes wrong.". In the end of the day you can also wrap all the unexpected exceptions to the some message for the user on your application layer and wrap the details about the database, to not be used by 3rd parties, etc.

My suggestion is have some ERR.

create or replace package ERR

   ci_NoDataFound constant int := -20100;
   NoDataFound exception;
   pragma exception_init(NoDataFound, -20100);

   procedure Raise;
   procedure Log;

end P_PRSRELIAB;

In your parent procedure, you will handle the excpetion of the current particular procedure, and no other ones.

BEGIN    
   SELECT *something* INTO *some variable*
   FROM *some table*
   WHERE *some field* = *user id*
   Call_Another_Procedure(*user id*, *some variable*)
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      ERR.Raise(-20100, 'unknown user id');         
END;

The procedure which is calling from the parent one, will handle only the excpetion of this particular procedure.

BEGIN    
   SELECT *something*
   FROM *some table*
   WHERE *some field* = *some user input*

EXCEPTION
   WHEN NO_DATA_FOUND THEN
      ERR.Raise(-20100, 'unknown some user input');  
END;

In the application layer, we will have proper messages - "uknown some user input" or "unknown user id". On the other side the trigger will log all the information about the particular exception.

Comments

[Andrew Martin]

Maybe my question wasn't clear. I'm doing all that already. My Log_And_Return method logs the problem with the full stacktrace then performs a raise_application_error. It's the fact when it gets raised, the when other block in the caller procedure will change the output message

[Andrew Martin]

Now, currently my Log_And_Return procedure does this logging and THEN raises an application error. So can I create a trigger that will simply detect if an application error has been thrown and, if so, return to the application layer (i.e. it doesn't need to worry about logging, since that will already be done)?

[mikron]

You can have a LOG procedure, which will just insert the log values (without raising). The exception raised from the db will return to caller instance.

[Andrew Martin]

Again, I'm already doing that. My "Log And Return" calls a Log procedure, THEN raises an application error. So am I looking to get a trigger that will detect when an application error is thrown and force a return to the application layer?

[mikron]

Remove all the when others clauses from the parent procedures. When you will raise NO_DATA_FOUND exception from the child procedure, it will return to Your application layer (and the trigger will log it - twice, one in the "Log And Return" procedure and one on the trigger side), so basically You can remove the logging part from Your exception handling procedure and leave all the logging in the trigger part. Please edit Your comment and remove the "Again" part. Thank You.

[Andrew Martin]

Too late to edit the comment. More importantly, you'd suggest removing ALL when others then? Simply let the application layer deal with any unknown exceptions? Is that right?

[mikron]

You are doing the same in workflow you described. You are returning to the application layer 'unknown_error'. If You ask me, it will be more useful to return the error message, which was received from the DB.

[mikron]

Let us continue this discussion in chat.

[Jeffrey Kemp]

No, @Lalit is correct. If you must have a WHEN OTHERS trigger, you can log the error but you should end it with a simple RAISE;. That way the real, unpredicted, unexpected unhandled exception gets raised to the caller. Your objective will be to add handlers for all the predicted, expected exceptions.

[Andrew Martin]

Ah, so you use -20000 for EVERY user defined error?

[Andrew Martin]

I suppose if you are doing that, in your first block of code you DON'T want to call Err.Log_And_Return again under "application_error", but just raise the error to the application layer (otherwise you'd log it twice). Is that correct?

[Lalit Kumar B]

@Andrew Martin, you just need to simply use RAISE in underlying procedures, so that when an error occurs, it would be raised and given to the caller. That is the main procedure. Otherwise you would end up logging the errors multiple times and make it difficult for yourself to debug.

[Andrew Martin]

True, but I want different error messages depending on where the error has occurred, so I can't just raise things. I want to raise them with a specific error. If I raise it with a specific error, then once it hits the "when others" block of the caller procedure, that error will be changed to whatever error is stated there - unless I simply reraise all "when others" errors.

[Jeffrey Kemp]

Yep. I don't know what your logger does; mine has some smarts that allows me to trace the execution hierarchy, which is why I always log every exit out of an exception handler.

[Rob van Wijk]

Hear hear! Only do exception handling at the entry points of your code (parent) and get rid of all that superfluous error handling at subroutines. Less is more.

[Andrew Martin]

Mine writes the stacktrace to the log, along with the user id, date and a few other things. I'm thinking with the approach in your answer, I could change my current system of having a number from -20999 to -20000 for all errors. Instead, I could have all errors under code -20000, but in a separate table give all my user errors a unique number, say from 1 to 1000. Then I can have a unique error code for all errors, but also an easy way to trace them through the program. Sound good?

[Jeffrey Kemp]

Ok, but the important thing to do is to ensure that your WHEN OTHERS handlers always do a RAISE; so they don't change the exception.

[Andrew Martin]

Yeah. What I'm thinking is that I can have something like a when application_error clause like in your example above, that would determine if a raise_application_error has been thrown and just raise it (as it's already been logged). Whereas the "when others" clause could log AND raise (as it wouldn't have been touched yet)