11 Replies Latest reply: Jul 23, 2014 12:34 AM by Ora-aff RSS

    Creating an Error Handling Architecture

    Ora-aff

      Hi, I am asked to create a Pl/SQL error handling architecture that should helpful while creating the application. SO far I can think of the following points: 1. A Table that will store information about error code, error message, user and date of exception for each exception raised 2. A Package with procedure that will log the records in the above table But I think that is not sufficient. What all things I should take into account for creating a good error handling architecture that would be really helpful in debugging the PL/SQL Programs?

        • 1. Re: Creating an Error Handling Architecture
          brunovroman

          Hello,

           

          you might have a look at Steven Feueurstein's column "Error Management" in March 2012 issue of Oracle Magazine, or search the Web with something like "Feueurstein error logging".

           

          HTH,

           

          Bruno Vroman.

          • 2. Re: Creating an Error Handling Architecture
            Ora-aff

            Thanks Brunovroman. I am going through it.

            • 3. Re: Creating an Error Handling Architecture
              rp0428

              Ora-aff wrote:

               

              Hi, I am asked to create a Pl/SQL error handling architecture that should helpful while creating the application. SO far I can think of the following points: 1. A Table that will store information about error code, error message, user and date of exception for each exception raised 2. A Package with procedure that will log the records in the above table But I think that is not sufficient. What all things I should take into account for creating a good error handling architecture that would be really helpful in debugging the PL/SQL Programs?

              Your question isn't clear. What does 'helpful while creating the application' mean?

               

              Exception handling is needed when EXECUTING the application. Why are you focusing on being helpful while 'creating' the app?

               

              The architecture of the exception handler will depend a great deal on the architecture and use of your application.

               

              Some exceptions are expected and handled (e.g. NO DATA FOUND). Are those to be logged also?  Other exceptions might be due to data validation issues when a web user inputs data items; why would you want to log those?

               

              What type of application is it?

              Is there a client-tier involved?

              Will exceptions be propagated back to the user?

              Who is responsible for resolving or troubleshooting exceptions?

              • 4. Re: Creating an Error Handling Architecture
                Mark D Powell

                Ora-aff, Bruno usually gives good advice.  I have not read or do not remember the content of Steve's article but what I recommend is creating a package/procedure that runs as an autonomous transaction that when passed an application ID and message with log this information along with the current timestamp into a an error logging table.  Because the insert into the error logging table is an anonymous transaction the data is saved without effecting the triggering transaction even when the triggering transaction fails and is rolled back.

                - -

                HTH -- Mark D Powell --


                • 5. Re: Creating an Error Handling Architecture
                  Ora-aff

                  rp0428 I agree here. My question is not clear. I will be able to answer your questions once I get more understanding of the application. It is still in specification phase. I only need to design the basic error handling mechanism that is generally followed by database developers. It might be upgraded over next couple of weeks depending on the application requirements. So just needed the basic approach towards it.

                  • 6. Re: Creating an Error Handling Architecture
                    Ora-aff

                    @Mark D Powell Thank you. That is good to start with. I have the following objects now:

                    A log table to store:

                     

                    Exception number
                    Exception message
                    The line on which the Exception occurred
                    The execution call stack
                    Time when the exception occurred
                    user

                     

                    There will be an autonomous procedure to insert records into the table.

                     

                    Additionally, there will be two more procedures for handling two different kinds of exception:

                    1. exception that needs to stop the execution of the program by RAISE after logging it

                    2. exception that  should not stop the program execution after logging it

                     

                    Both these procedure will call the main procedure to log exception details into log table.

                    • 7. Re: Creating an Error Handling Architecture
                      Ora-aff

                      My Table DDL is:

                       

                      create table logs (

                      id                       number,

                      err_code             varchar2,

                      err_message       varchar2,

                      err_call_stack     varchar2,

                      err_time              date,

                      err_user              varchar2

                      );

                       

                      ID columns is a serial number populated by a sequence.

                      I need to implement a logic such that id is populated by a new number for a user for that session. After that the same id will be used for logging the exceptions raised by that user throughout the session. There will be new id for each user's session.

                       

                      Example - user abc connects to the session and in his session 3 exceptions are raised and for user xyz two exceptions are raised so the table entries will read something like this:

                       

                      id           err_code        user            

                       

                      1           12345            abc

                      1           34568            abc

                      1           35675            abc

                      2           56738            xyz

                      2         89783           xyz
                       

                       

                       

                      How do I implement this logic in my procedure?

                      • 8. Re: Creating an Error Handling Architecture
                        Mike Kutz

                        "id" is too generic.  Use Session_ID instead.

                        DATE is to coarse.  Go with TIMESTAMP.

                         

                        The application will have to set the session_id and actual userid for each session.

                        You use sys_context() to set and retrieve the values for those fields. (and others.. as needed)

                         

                        This means that the application will need to initialize each session when a user logs in/out (*cough* VPD *cough*).

                        If you were to use APEX as you application framework., the two values (user and session_id) are automatically set.

                         

                        your homework:

                        research sys_context and VPD.  (they go hand-in-hand)

                         

                        Although you may not need to implement VPD policies right now, it is always good to know what the DB can do out-of-the-box.

                         

                        MK

                        • 9. Re: Creating an Error Handling Architecture
                          Mark D Powell

                          One consideration might be storing the program name that captured the error rather than rely on the call stack.  We call it application and we can search for all message for a specific program for a specific date range when working on a problem.

                          - -

                          We also purge the messages using a different age criteria based on the source program.  That is where most all data is deleted when it passes 31 days in age for a few program we hang onto the data for 62 or 92 days.

                          - -

                          HTH -- Mark D Powell --

                          • 10. Re: Creating an Error Handling Architecture
                            rp0428

                            How do I implement this logic in my procedure?

                            Use a sequence for the ID column. I provided sample code for this type of logging in threads a year or so ago. Read my replies in those threads and you will have a complete set of sample logging code.

                            https://forums.oracle.com/forums/thread.jspa?threadID=2485174

                            I usually use this method

                             

                            1. Create a SQL type for logging information

                            2. Put the package name into a constant in the package spec

                            3. Add a line to each procedure/function for the name.

                             

                            Sample package spec

                            The other thread has the TYPE DDL

                            https://community.oracle.com/message/10951306?

                            The second way is to ALWAYS include logging calls in your code. These call a LOG package procedure. It is the LOG package itself that controls the level of logging and you can change it during the session.

                             

                            This isolates the control of the logging so your application code doesn't deal with it. The log functions in the LOG package are written to perform different amounts or types of logging based on what hte LOG_LEVEL is.

                             

                            I provided some template code that shows how this is used in this thread from January

                            How to get a called procedure/function name within package?

                             

                            Also see my reply in this thread from last year.

                             

                            The package code could also use a separate procedure for each level. The appropriate procedure could be called directly by the app code if you know what level should be used (prevents developers from accidentally passing the wrong 'level' parameter) or, more often, the appropriate procedure is called by the top-level log procedure based on the current log level. See the links above for explanation of the 'TYPE_LOG_INFO' custom type.

                            How to get a called procedure/function name within package?

                            Sample SQL type

                            DROP TYPE TYPE_LOG_INFO; CREATE OR REPLACE TYPE TYPE_LOG_INFO AUTHID DEFINER AS OBJECT (  
                            • 11. Re: Creating an Error Handling Architecture
                              Ora-aff

                              Thanks everyone. I am going through the resources provided. I will come up with the logic to generate the values for ID column.