create table caught_errors (
dt date,
username varchar2( 30), -- value from ora_login_user
msg varchar2(512),
stmt varchar2(512)
);
create or replace trigger catch_errors
after servererror on database
declare
sql_text ora_name_list_t;
msg_ varchar2(2000) := null;
stmt_ varchar2(2000) := null;
begin
for depth in 1 .. ora_server_error_depth loop
msg_ := msg_ || ora_server_error_msg(depth);
end loop;
for i in 1 .. ora_sql_txt(sql_text) loop
stmt_ := stmt_ || sql_text(i);
end loop;
insert into
caught_errors (dt , username ,msg ,stmt )
values (sysdate, ora_login_user,msg_,stmt_);
end;
/
Note that whenever a SQL error occurs on the database this trigger will fire and:
1) try to stuff the entire SQL statement that failed into a varchar2(2000), regardless of how big it actually is
2) if that doesn't blow up, then tries to insert the same value into a varchar2(255) column
In SQL Plus, this leads to errors like this (using an invalid table name in a large SELECT statement):
ERROR at line 30: ORA-00604: error occurred at recursive SQL level 1 ORA-01401: inserted value too large for column ORA-06512: at line 12 ORA-00942: table or view does not exist
... which is clearly more informative than:
ERROR at line 30: ORA-00942: table or view does not exist
5 comments:
That would work fine if you used substr to truncate the values being inserted into your table to the size of the columns (thus ensuring you didnt try to insert something too large), like:
insert into caught_errors
(dt,username,msg,stmt)
values (sysdate, ora_login_user, substr(msg_,1,100),
substr(stmt_,1,100));
...until you run out of tablespace for "caught_errors".
...and assuming you're only interested in the first 100 characters of the message, and the first 100 characters of the statement.
...and as long as the statement isn't longer than 2000 characters in the first place.
And what about the recursive execution of this when there is a sql error in the trigger itself, which will trigger another execution of itself, etc.
Post a Comment