Monday, November 26, 2012

How to Capture SQL Error

Here is a way to capture session errors except following errors (modified from here):
ORA-01034 ORACLE not available
ORA-01403 no data found
ORA-01422 exact fetch returns more than requested number of rows,
ORA-01423 error encountered while checking for extra rows in exact fetch
ORA-04030 out of process memory when trying to allocate string bytes.

create table myschema.caught_errors (
  dt        date,              
  username  varchar2( 30),
  msg       varchar2(4000),
  stmt      varchar2(4000)

create or replace trigger myschema.catch_errors
   after servererror on database
   sql_text ora_name_list_t;
   msg_     varchar2(4000) := null;
   stmt_    varchar2(4000) := null;
   i int;
  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
    if (nvl(length(stmt_),0) + length(sql_text(i))) < 4000 then
        stmt_ := stmt_ || sql_text(i);
        insert into     caught_errors (dt     , username      ,msg ,stmt )
           values (sysdate, ora_login_user,msg_,stmt_);
         stmt_ :=sql_text(i);
    end if;
  end loop;
  i:= ora_sql_txt(sql_text);
  insert into
    caught_errors (dt     , username      ,msg ,stmt )
           values (sysdate, ora_login_user,msg_,stmt_);

alter trigger myschema.catch_errors disable;
truncate table myschema.caught_errors;

No comments: