Background:
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.
Problem:
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;
Question:
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?
Log_And_Return
to re-raise errors?