Oracle Stored procedure Record exception

For Oracle stored procedures, throwing exceptions can be thrown into code or logged with a table. If your system application has a lot of nodes, like 40 of our nodes, if the wrong throw into the code, the error can not be found at all, it is best to record the error table. This feature of logging errors is best common.

--Create Error log table

CREATE TABLE Pub_proc_err_log
(
LOG_ID number,
Module_name VARCHAR2 (100),
Proc_name VARCHAR2 (100),
Err_time DATE,
Sql_code VARCHAR2 (50),
SQL_ERRM VARCHAR2 (100),
Err_content VARCHAR2 (500)
);
Comment on column pub_proc_err_log. LOG_ID is ' primary key ';
Comment on column pub_proc_err_log. Module_name is ' module name ';
Comment on column pub_proc_err_log. Proc_name is ' stored procedure name ';
Comment on column pub_proc_err_log. Err_time is ' Error time ';
Comment on column pub_proc_err_log. Sql_code is ' SQLCODE ';
Comment on column pub_proc_err_log. SQL_ERRM is ' sqlerrm ';
Comment on column pub_proc_err_log. Err_content is ' Error specific line ';

--sequence of table primary keys

Create sequence Seq_record_proc_err
MinValue 1
MaxValue 9999999999999999999999999999
Start with 21
Increment by 1
Cache 20;

--Common logging of error stored procedures

CREATE OR REPLACE PROCEDURE
Record_proc_err_log (Module_name varchar2,
Proc_name VARCHAR2,
V_sqlcode VARCHAR2,
V_SQLERRM VARCHAR2,
V_err_line varchar2) is
PRAGMA autonomous_transaction;
BEGIN
INSERT INTO Pub_proc_err_log
(LOG_ID,
Module_name,
Proc_name,
Err_time,
Sql_code,
SQL_ERRM,
Err_content)
Values
(Seq_record_proc_err.nextval,
Module_name,
Proc_name,
Sysdate,
V_sqlcode,
V_SQLERRM,
V_err_line);
Commit
END Record_proc_err_log;

--To test

Create or replace procedure TEST_P1 is
Begin
Execute IMMEDIATE ' select from test ';
exception
When others then
Record_proc_err_log (' Module name ', ' Test_p1 ', SQLCODE,SQLERRM,
SUBSTR (Dbms_utility.format_error_backtrace, 1, 400));
End TEST_P1;

sql> Col proc_name format A8;
sql> Col err_time format A10;
sql> Col sql_code format A5;
sql> Col sql_errm format A22;
sql> Col err_content format A42;
Sql> select Proc_name,err_time,sql_code,sql_errm,err_content from Pub_proc_err_log;
Proc_nam err_time Sql_c sql_errm err_content
-------- ---------- ----- ---------------------- ------------------------------------------
TEST_P1 August-December -14-936 ORA-00936: Missing expression ORA-06512: in Lcam_test. Test_p1 ", line 3

Oracle Stored procedure Record exception