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?

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?

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.

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.

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:

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.

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

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.