Various techniques to store Debug log message to handle Errors - Part 2

There are various way to capture user define messages to debug the code or to display the errors encounter during the execution of code.

In this scenario I am using one common framework to handle errors as well as progress of execution flow. To achieve this functionality I have created three custom tables one common package which is used across the instance.

Below are the Required Database Objects:

Error Handling Tables:

xx_process_log:

This table is used to track the progress of Procedure/Function execution flow. This is first table where data is inserted having statuses like‘Start’ and after execution ‘Complete’ Or ‘Error’.

It has one unique message id which is shared by other two tables to link messages i.e. for unique reference.

xx_message_log:

This Table is used to store random user friendly messages like DBMS_OUTPUT.PUT_LINE. It has message id which is common taken from xx_process_log for each Procedure/Function flow.

xx_error_log:

This Table is used to store standard Errors i.e. exception message with sqlcode and sqlerrm. It has message id which is common taken from xx_process_log for each Procedure/Function flow.

Common Package:

xx_common_error_handle:

This package is used to Store logic for Inserting data into three error handling tables. It has three separate procedures to insert data into xx_process_log,xx_message_log and, xx_error_log.

Mandatory Common Procedure in all packages where we have to use this Framework:

xx_process_track_log:

This Procedure is common and mandatory in all Packages where we have to use Error handling framework. This is called by each Procedure or Function at start and at end to update the record into table xx_process_log.

When first time we called, it will insert details (as per your requirement you can keep in parameter) in table xx_process_log with status ‘Start’ and it has one sequence which generate unique message id and store in table. Also this message id is required OUT parameter, as we will use this message id as a reference while inserting data into table’s xx_message_log and, xx_error_log.

(Note: This is very basic example which can be enhanced as per business requirements for

e.g.

a) You can use some profile options or lookup to control these messages.

b) You can send email notification when error occurs

c) You can send data to dashboard for display using business events.

)