Error Logging and Alerts in Oracle

Error Logging is main part of our database application. I am trying to share some sample code which I am using in my daily life.
We need to follow below steps.
1) Create Error Log Table.
Table: Error_Log
Columns:

1. Log_ID
   1. Identity column.
2. HOST_NAME
   1. Host Name.
3. SERVICE_NAME
   1. Service Name.
4. USER_NAME
   1. User Name.
5. PROC_NAME
   1. Procedure Name.
6. SQL_CODE
   1. Error Code from Procedure.
7. SQL_ERRM
   1. Error Message from Procedure.
8. ERROR_BACKTRACE
   1. Error Line Number from Procedure.
9. Created_Date
   1. Default current date from database.

2) Create Sequence for Error_Log Table.
3) Create trigger on Error Log table to set sequence to Log_ID on insert.
4) Create Error Log Procedure
5) Create Procedure which used Error Log Procedure in 3rd Step.
6) Execute Parent Procedure. Below is error output from Parent Procedure.

7) Check logs in Error Log table.

8) Create Config Table.
Table: Error_Log
Columns:

1. CONFIG_ID
   1. Identity column
2. CONFIG_KEY
   1. Config Key
3. CONFIG_VALUE
   1. Config Value
4. IS_ACTIVE
   1. 1 = Config Key is active
   2. 0 = Config Key is inactive
5. CREATED_DATE
   1. Default current date from database.
6. MODIFIED_DATE

9) Create Sequence for Config Table.
10) Create trigger on Config table to set sequence to Config_ID on insert.
11) Insert mail configuration data into Config Table.

1. Mail_Smtp_Host
2. Mail_From
3. Mail_To
4. Mail_Subject_Error_Log_Alert

12) Create string array type.
13) Create function split
14) Create Procedure to send mails.
15) Create Procedure to send alert mails.
16) Create job to send error log alert daily once at 7:00 A.M.
17) Execute job to send alert mail. Below is alert mail.
Below are the scripts to download.

1. Oracle Error Logging and Alerts.sql

Note

1. Before executing attached script replace your email id.
2. You should have ACL created on your schema. (Check http://www.sqlscientist.com/2013/07/how-to-create-aclaccess-control-list-in.html)