Oracle 错误日志表及异常处理包详解 附源码

说明：

1. 测试中，共有 2 个用户 -- 模拟实际开发场景

   (1) odsdata: 存放业务数据

   (2) odscde : 执行具体操作

   -- 为了方便测试，也可以去掉所有 '属主 owner'

CREATE TABLE odsdata.ods_program_error_log (

  error_log_id    VARCHAR2(10) NOT NULL , -- CONSTRAINT pk_opel_error_log_id PRIMARY KEY(error_log_id)

  owner        VARCHAR2(30),

  package_name    VARCHAR2(30),

  procedure_name   VARCHAR2(30),

  error_comment    VARCHAR2(1000),

  error_backtrace   VARCHAR2(400),

  error_stack     VARCHAR2(4000),

  call_stack     VARCHAR2(4000),

  error_date     DATE NOT NULL ,

  oracle_execute_user VARCHAR2(50),

  um_id        VARCHAR2(50)

);

COMMENT ON TABLE odsdata.ods_program_error_log IS '程序错误日志表' ;

COMMENT ON COLUMN odsdata.ods_program_error_log.error_log_id IS '错误日志id' ;

COMMENT ON COLUMN odsdata.ods_program_error_log.owner IS '属主' ;

COMMENT ON COLUMN odsdata.ods_program_error_log.package_name IS '包名' ;

COMMENT ON COLUMN odsdata.ods_program_error_log.procedure_name IS '过程名' ;

COMMENT ON COLUMN odsdata.ods_program_error_log.error_comment IS '错误备注' ;

COMMENT ON COLUMN odsdata.ods_program_error_log.error_backtrace IS '错误跟踪' ;

COMMENT ON COLUMN odsdata.ods_program_error_log.error_stack IS '错误堆栈' ;

COMMENT ON COLUMN odsdata.ods_program_error_log.call_stack IS '调用堆栈' ;

COMMENT ON COLUMN odsdata.ods_program_error_log.error_date IS '错误时间' ;

COMMENT ON COLUMN odsdata.ods_program_error_log.oracle_execute_user IS 'oracle执行用户' ;

COMMENT ON COLUMN odsdata.ods_program_error_log.um_id IS '操作人员um账号' ;

GRANT SELECT , INSERT , UPDATE ON odsdata.ods_program_error_log TO odscde;

CREATE OR REPLACE PACKAGE odscde.pkg_ods_error_handle IS

  --*************************************************

  --功能说明: 错误日志

  --参数说明: i_procedure_name 程序名

  --     i_error_comment  错误备注（手工添加的）

  --调用函数:

  --修改记录： create by YoYo 2020-12-17

  --*************************************************

  PROCEDURE exception_handle(i_procedure_name IN VARCHAR2,

                i_error_comment IN VARCHAR2);

END pkg_ods_error_handle;

CREATE OR REPLACE PACKAGE BODY odscde.pkg_ods_error_handle IS

  --*************************************************

  --功能说明: 错误日志

  --参数说明: i_procedure_name 程序名

  --     i_error_comment  错误备注（手工添加的）

  --调用函数:

  --修改记录： create by YoYo 2020-12-17

  --*************************************************

  PROCEDURE exception_handle(i_procedure_name IN VARCHAR2,

                i_error_comment IN VARCHAR2) IS

   PRAGMA AUTONOMOUS_TRANSACTION; -- !!! 自治事务

   v_log_info odsdata.ods_program_error_log%ROWTYPE;

  BEGIN

   v_log_info.error_log_id    := '1' ; -- 异常错误id（一般是 "获取序列号"，此处仅演示）

   v_log_info.procedure_name   := i_procedure_name; -- 程序名

   v_log_info.error_comment    := i_error_comment;

   v_log_info.oracle_execute_user := sys_context( 'USERENV' , 'SESSION_USER' ); -- oracle执行用户

   v_log_info.um_id        := nvl(sys_context( 'USERENV' , 'OS_USER' ),

                      USER ); -- 操作人员um账号

   v_log_info.error_backtrace   := dbms_utility.format_error_backtrace; -- 错误跟踪

   v_log_info.error_stack     := dbms_utility.format_error_stack; -- 错误堆栈

   v_log_info.call_stack     := dbms_utility.format_call_stack; -- 调用堆栈

   v_log_info.error_date     := SYSDATE;

   -- 可选列

   ---- 包属主

   v_log_info.owner := substr(v_log_info.error_backtrace,

                 instr(v_log_info.error_backtrace, '"' , 1) + 1,

                 (instr(v_log_info.error_backtrace, '.' , 1) -

                 instr(v_log_info.error_backtrace, '"' , 1) - 1));

   ---- 包名

   v_log_info.package_name := substr(v_log_info.error_backtrace,

                    instr(v_log_info.error_backtrace,

                       '.' ,

                       1) + 1,

                    (instr(v_log_info.error_backtrace,

                        '"' ,

                        1,

                        2) - instr(v_log_info.error_backtrace,

                              '.' ,

                              1) - 1));

   -- 插入数据

   INSERT INTO odsdata.ods_program_error_log

    (error_log_id,

     owner,

     package_name,

     procedure_name,

     error_comment,

     error_backtrace,

     error_stack,

     call_stack,

     error_date,

     oracle_execute_user,

     um_id)

   VALUES

    (v_log_info.error_log_id,

     v_log_info.owner,

     v_log_info.package_name,

     v_log_info.procedure_name,

     v_log_info.error_comment,

     v_log_info.error_backtrace,

     v_log_info.error_stack,

     v_log_info.call_stack,

     v_log_info.error_date,

     v_log_info.oracle_execute_user,

     v_log_info.um_id);

   COMMIT ;

  END exception_handle;

END pkg_ods_error_handle;

CREATE TABLE odsdata.stu_info (

  sno  NUMBER(10) CONSTRAINT pk_si_sno PRIMARY KEY ,

  sname VARCHAR2(50) NOT NULL

);

GRANT SELECT , INSERT , UPDATE ON odsdata.stu_info TO odscde;

INSERT INTO odsdata.stu_info(sno, sname) VALUES (1, '瑶瑶' );

COMMIT ;

CREATE OR REPLACE PACKAGE odscde.pkg_ods_error_test AS

  PROCEDURE ods_error_test(o_flag  OUT VARCHAR2,

               o_message OUT VARCHAR2);

END pkg_ods_error_test;

CREATE OR REPLACE PACKAGE BODY odscde.pkg_ods_error_test AS

  PROCEDURE ods_error_test(o_flag  OUT VARCHAR2,

               o_message OUT VARCHAR2) IS

   i_procedure_name VARCHAR2(30) := 'ods_error_test' ;

  BEGIN

   INSERT INTO odsdata.stu_info (sno, sname) VALUES (1, '瑶瑶' );

   COMMIT ;

   o_flag  := 'Y' ;

   o_message := '执行成功！' ;

  EXCEPTION

   WHEN OTHERS THEN

    o_flag  := 'N' ;

    o_message := '执行失败！' ;

    pkg_ods_error_handle.exception_handle(i_procedure_name => i_procedure_name,

                       i_error_comment => '' -- 关键入参、出参

                       );

  END ods_error_test;

END pkg_ods_error_test;