----------------------------------------------------------------------------------
--- Filename: log_errors.sql
--- Purpose: captures all database errors and writes error
--- message and statement to a table alowing error analysis
--- Oracle9i and above
--- Date: January 10, 2007
--- Author: Nake Micev (nake.micev@codex.com.mk)
----------------------------------------------------------------------------------
drop trigger error_capture;
drop table error_log;
create table ERROR_LOG
(
TIME_STAMP DATE not null,
APP VARCHAR2(80),
TERMINAL VARCHAR2(30),
OS_USER VARCHAR2(30),
ORACLE_USER VARCHAR2(30),
ERROR_TEXT CLOB,
STMT_TEXT CLOB
)
tablespace TOOLS
pctfree 10
pctused 40
initrans 1
maxtrans 255
storage
(
initial 512K
minextents 1
maxextents unlimited
);
CREATE OR REPLACE TRIGGER ERROR_CAPTURE
AFTER SERVERERROR ON DATABASE
DECLARE
v_curr_sid number;
v_sys_time date;
v_terminal varchar2(15);
v_app_name varchar2(80);
v_os_user varchar2(30);
l_err long;
l_stmt long;
l_sql_text ora_name_list_t;
l_n number;
BEGIN
select sid into v_curr_sid from v$mystat where rownum = 1;
select sysdate, substr(program, 1, 80), substr(osuser, 1, 30), substr(terminal, 1, 15)
into v_sys_time, v_app_name, v_os_user, v_terminal from v$session where sid = v_curr_sid;
l_n := ora_sql_txt(l_sql_text);
for i in 1 .. l_n
loop
l_stmt := l_stmt || l_sql_text(i);
end loop;
l_err:=l_err||dbms_utility.format_error_stack;
insert into error_log values (v_sys_time, v_app_name,v_terminal , v_os_user, user, l_err, l_stmt);
END;
/
|