--建立错误日志表
2
3 create table PUB_PROC_ERR_LOG
4 (
5 LOG_ID NUMBER,
6 MODULE_NAME VARCHAR2(100),
7 PROC_NAME VARCHAR2(100),
8 ERR_TIME DATE,
9 SQL_CODE VARCHAR2(50),
10 SQL_ERRM VARCHAR2(100),
11 ERR_CONTENT VARCHAR2(500)
12 );
13 comment on column PUB_PROC_ERR_LOG.LOG_ID is ‘
主键‘;
14 comment on column PUB_PROC_ERR_LOG.MODULE_NAME is ‘
模块名称‘;
15 comment on column PUB_PROC_ERR_LOG.PROC_NAME is ‘
存储过程名称‘;
16 comment on column PUB_PROC_ERR_LOG.ERR_TIME is ‘
报错时间‘;
17 comment on column PUB_PROC_ERR_LOG.SQL_CODE is ‘
SQLCODE‘;
18 comment on column PUB_PROC_ERR_LOG.SQL_ERRM is ‘
SQLERRM‘;
19 comment on column PUB_PROC_ERR_LOG.ERR_CONTENT is ‘
报错的具体行‘;
20
21 --表主键的序列
22
23 create sequence SEQ_RECORD_PROC_ERR
24 minvalue 1
25 maxvalue 9999999999999999999999999999
26 start with 21
27 increment by 1
28 cache 20;
29
30 --通用记录错误存储过程
31
32 CREATE OR REPLACE PROCEDURE
33 record_proc_err_log(module_name varchar2,
34 proc_name varchar2,
35 v_SQLCODE varchar2,
36 v_SQLERRM varchar2,
37 v_err_line varchar2) is
38 PRAGMA AUTONOMOUS_TRANSACTION;
39 BEGIN
40 insert into pub_proc_err_log
41 (log_id,
42 module_name,
43 proc_name,
44 err_time,
45 sql_code,
46 sql_errm,
47 err_content)
48 values
49 (seq_record_proc_err.nextval,
50 module_name,
51 proc_name,
52 sysdate,
53 v_SQLCODE,
54 v_SQLERRM,
55 v_err_line);
56 commit;
57 END record_proc_err_log;
58
59 --进行测试
60
61 create or replace procedure test_p1 is
62 begin
63 execute IMMEDIATE ‘
select from test‘;
64 exception
65 when others then
66 record_proc_err_log(‘
模块名‘,‘
test_p1‘,SQLCODE,SQLERRM,
67 substr(dbms_utility.format_error_backtrace, 1, 400));
68 end test_p1;
69
70 SQL> col proc_name format a8;
71 SQL> col err_time format a10;
72 SQL> col sql_code format a5;
73 SQL> col SQL_ERRM format a22;
74 SQL> col ERR_CONTENT format a42;
75 SQL> select proc_name,err_time,sql_code,SQL_ERRM,ERR_CONTENT from pub_proc_err_log;
76 PROC_NAM ERR_TIME SQL_C SQL_ERRM ERR_CONTENT
77 -------- ---------- ----- ---------------------- ------------------------------------------
78 test_p1 08-12月-14 -936 ORA-00936: 缺失表达式 ORA-06512: 在 "
LCAM_TEST.TEST_P1", line 3
79 Oracle存储过程记录异常
标签：test trace values 12月 ora select content tar val
