Tuesday, March 07, 2006

Error-Prone Error Handling

A colleague found this handy utility on the internet. The idea is that whenever you get an Oracle error, the errant SQL statement will be written away to a table along with the message for you to read and enjoy at your leisure. Which would be fine I suppose, if it didn't introduce a whole lot of errors of its own.
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:

Ray said...

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));

Thai Rices said...

...until you run out of tablespace for "caught_errors".

Thai Rices said...

...and assuming you're only interested in the first 100 characters of the message, and the first 100 characters of the statement.

Thai Rices said...

...and as long as the statement isn't longer than 2000 characters in the first place.

Johan said...

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.