I've used a few Oracle articles to develop an error package, with some procedures. One of these is Log_And_Return which is called throughout the database. I've simplified it for brevity, and it looks like this:
PROCEDURE Log_And_Return (error_name) IS BEGIN // Some code to convert error_name to error_code and error_message LOG (error_code, error_message); Raise_To_Application (error_code, error_message); END Log_And_Return; // In reality, this procedure takes parameters which determine whether it will log and/or raise.
The Log procedure does as it says and the Raise_To_Application procedure simply raises an application error.
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* EXCEPTION WHEN NO_DATA_FOUND THEN ERR.Log_And_Return('unknown_id'); WHEN OTHERS THEN ERR.Log_And_Return('unknown_error'); END;
So I'm displaying a specific error message if no data is found, or a generic one is another problem has occurred.
Now, let's say I amend the code:
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 a new procedure. Inside it, 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;
If the no_data_found error is thrown, Log_And_Return will log the problem and raise an application error... 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?
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 re raised the most recent error (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).
Or do I simply get rid of ALL when others blocks and let the application layer deal with any of these unknown exceptions?