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.

Answer 1

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.

Answer 2

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;

Answer 3

You need to re-raise the error in the underlying procedures using RAISE.

When an error occurs, and if you have an exception block, the handle moves to the exception block. The caller will remain unaware until you re-raise it using RAISE.

keep all the underlying procedures inside BEGIN-END block.

Also, use dbms_utility.format_error_stack and dbms_utility.format_error_backtrace to get the call stack.