1.首先创建用于保存错误信息的表
1 | CREATE TABLE TBL_PROC_ERRMSG |
2 | ( |
3 | BIZ_CODE VARCHAR2(50), |
4 | ERR_LINE VARCHAR2(10), |
5 | ERR_CODE VARCHAR2(10), |
6 | MSG VARCHAR2(200), |
7 | CRT_TM DATE DEFAULT SYSDATE |
8 | ); |
01 | CREATE OR REPLACE PROCEDURE PROC_SAVE_ERRMSG(BIZCODE IN VARCHAR2, |
02 | ERRORLINE IN VARCHAR2, |
03 | ERRORCODE IN VARCHAR2, |
04 | MSG IN VARCHAR2) IS |
05 | /*必须要使用自治事务,否则 commit 会影响调用程序事务*/ |
06 | PRAGMA AUTONOMOUS_TRANSACTION; |
07 | BEGIN |
08 | INSERT INTO TBL_PROC_ERRMSG |
09 | (BIZ_CODE, ERR_LINE, ERR_CODE, MSG) |
10 | VALUES |
11 | (BIZCODE, ERRORLINE, ERRORCODE, MSG); |
12 | COMMIT ; |
13 | END ; |
1 | --存过过程实现主体 |
2 | EXCEPTION |
3 | WHEN OTHERS THEN |
4 | PROC_SAVE_ERRMSG(biz_code/*此变量用于查找错误记录标记*/,DBMS_UTILITY.format_error_backtrace,sqlcode,SQLERRM); |
5 | END ; |