基于Oracle异常/错误日志采集

CREATE TABLE duliang.ods_program_error_log (
error_log_id VARCHAR2(10) NOT NULL, — CONSTRAINT pk_opel_error_log_id PRIMARY KEY
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 duliang.ods_program_error_log IS ‘程序错误日志表’;
COMMENT ON COLUMN duliang.ods_program_error_log.error_log_id IS ‘错误日志id’;
COMMENT ON COLUMN duliang.ods_program_error_log.owner IS ‘属主’;
COMMENT ON COLUMN duliang.ods_program_error_log.package_name IS ‘包名’;
COMMENT ON COLUMN duliang.ods_program_error_log.procedure_name IS ‘过程名’;
COMMENT ON COLUMN duliang.ods_program_error_log.error_comment IS ‘错误备注’;
COMMENT ON COLUMN duliang.ods_program_error_log.error_backtrace IS ‘错误跟踪’;
COMMENT ON COLUMN duliang.ods_program_error_log.error_stack IS ‘错误堆栈’;
COMMENT ON COLUMN duliang.ods_program_error_log.call_stack IS ‘调用堆栈’;

COMMENT ON COLUMN duliang.ods_program_error_log.error_date IS ‘错误时间’;

异常处理package
CREATE OR REPLACE PACKAGE duliang.pkg_ods_error_handle IS
—
–功能说明: 错误日志
–参数说明: i_procedure_name 程序名
— i_error_comment 错误备注（手工添加的）
—
PROCEDURE exception_handle(i_procedure_name IN VARCHAR2,
i_error_comment IN VARCHAR2);
END pkg_ods_error_handle;
/
CREATE OR REPLACE PACKAGE BODY duliang.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 duliang.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 duliang.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 duliang.xx (
sno NUMBER(10) CONSTRAINT pk_si_sno PRIMARY KEY,
sname VARCHAR2(50) NOT NULL
);
GRANT SELECT, INSERT, UPDATE ON duliang.xx TO duliang;

INSERT INTO duliang.xx(sno, sname) VALUES(1, ‘如人饮水冷暖自知’);

COMMIT;

CREATE OR REPLACE PACKAGE duliang.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 duliang.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 duliang.xx (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;