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

1 概述

1. 目的：’快速定位程序异常’

2. 包处理的核心思想：’自治事务’ — 自治事务的 “提交、回滚” 与主事务之间互不影响

3. 错误异常记录逻辑大体一致，此处记录，方便需要使用时复制、粘贴

4. 验证思路：通过执行报错的过程，观察 ‘程序执行结果’ 和 ‘日志表’ 数据插入情况

2 效果演示

程序执行截图：

日志表查询截图：

3 源码

说明：1. 测试中，共有 2 个用户 -- 模拟实际开发场景  (1) odsdata: 存放业务数据  (2) odscde : 执行具体操作  -- 为了方便测试，也可以去掉所有 '属主 owner'

3.1 错误日志表

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;

3.2 异常处理包

扩展：oracle 序列详解（sequence）

package：

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;

package body：

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;

3.3 测试程序

演示报错：违反唯一性约束

stu_info：

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;

package:

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;