3 Replies Latest reply: Mar 21, 2010 8:00 AM by Hoek RSS

    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.
        • 1. Re: exception handler package
          Hi !

          Just few ideas ..

          Your ERRORS_TABLE is probably the only way .. You can in fact load all the errors in memory but if you have an serious application .. errors should be in table

          You can include in the error table some key like language , which is meant like the languge of error msg if you are dealing with multilanguage app

          Somewhere in app ( in some package GLOBAL for example ) you should define memory structure - cache and when the error is first time call you can write it into cache and then when the error raise again you can get errm from your cache. For this option you should consider few things .. maybe your error_table will always resides in "system" cache .. if you have web app your cache will probably be almost always empty .. depends on app ,you should check what is the performance effect ..

          You should create an UI over error table

          For handling oracle errors .. if you want to diferentiate oracle-errors from user defined .. for user defined exception code could only be -20000 .. -20999

          O and of course for key in your table use code of course and not errm


          Edited by: ttt on 20.3.2010 15:19

          Well i'm thinking about that table .. if you expect that you will change errmsgs often , or in fact ..add from time to time translation to your errm than
          yes as you have present your problem .. you should have table ... else of course there is a possibility that you hardcode everything in a package provided for this purpose ..
          • 2. Re: exception handler package
            Take a look at the demos here:
            near the bottom of the page.

            Also look at the following:
            WHO_CALLED_ME - http://www.morganslibrary.org/reference/owa_util.html

            Predefined Inquiry Directives - http://www.morganslibrary.org/reference/dbms_db_version.html
            • 3. Re: exception handler package