exception handler package
I would like your opinions/suggestions for improvements/critiques on the following strategy for a global exception handler package:
I am planning to write a separate exception handling package. In the package I would declare my custom exceptions. The package should also handle standard oracle exeptions(as opposed to custom exceptions). I would declare my custome exceptions as follows:
EXCEPTION invalid_cust_num;
PRAGMA EXPETION INIT ( invalid_cust_num, -20000);
I now need to write a procedure in the package that would be called by PL/SQL code. The proc would say write records to an error_log table and then RAISE the exception so that the java calling layer would be notified. I also need to associate the exception with an sql error message so that the java calling layer is provided with a description. My questions are:
1. How do I associate my custom exceptions with error message text ina flexible way so it is easily mainted and centralized as opposed to hard coding. Maybe in a PLSQL_ERROR table ( error_code number, error_name varchar2(30), error_msg varchar(2000)) which stores the -20000 to -20999 numbers with a column for the error text?
2. If I do 1 above then what parameter do I pass the package proc fro my PL/SQL code? the error code say -20001 which is cryptic OR the custom error name as a varchar2 parameter i.e. 'invalid_cust_num'? I would say passing the error name is more readable. In the PL/SQL code if I get a NON custom oracle error I dont have a error_name but I have the SQL ERROR number and the SQL ERROR MESSAGE so my exception handling package has to differentiate between the two types of calls. ANy advice how I handle this?
3. If I do 2 above, in the exception handler package proc I could look up this string or number in the PLSQL_ERROR table. Now I have the error_code and error_msg in PL/SQL variables V_ERROR_CODE and V_ERROR_MSG and use RAISE_APPLICATION_ERROR(v_error_code,v_error_msg, TRUE). I am assuming that the RAISE_APPLICATION_ERROR can take variables as parameters and not just constants and strings literals.
4. The only shortcoming is that I have to manually maintain the PLSQL_ERROR table every time I add or delete or change my custom error declarations. I could write another peice of code to scan my excepton package spec/header and auto load the PLSQL_ERROR table. I would have to add the error_msg as a comment beside each custom exception declaration and pick up the comment for the error_msg column.
Thanks in anticipation.