Oracle Stored Procedure record exception, Oracle Stored Procedure record

Oracle Stored Procedure record exception, Oracle Stored Procedure record

For Oracle stored procedures, an exception can be thrown to the code or recorded in a table. If your system application has many nodes, such as our 40 nodes, if an error is thrown into the code, the error cannot be found at all. It is best to use a table to record the error. This function should be common.

-- Create an 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 reporting 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 'specific row for which an error is returned ';

-- Sequence of Table Primary keys

Create sequence SEQ_RECORD_PROC_ERR
Minvalue 1
Max value 9999999999999999999999999999
Start with 21
Increment by 1
Cache 20;

-- General record error storage process

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;

-- 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-14-936 ORA-00936: Missing expression ORA-06512: In "LCAM_TEST.TEST_P1", line 3