Friday, August 15, 2008

Catch system errors with an After Servererror trigger

create table t ( msg varchar2(4000) );

create or replace trigger snapshot_too_old
after servererror on database
declare
l_sql_text ora_name_list_t;
l_n number;
begin
if ( is_servererror(1555) )
then
insert into t values ( 'ora_sysevent = ' || ora_sysevent );
insert into t values ( 'ora_login_user = ' || ora_login_user );
insert into t values ( 'ora_server_error = ' || ora_server_error(1) );

l_n := ora_sql_txt( l_sql_text );
for i in 1 .. l_n
loop
insert into t values ( 'l_sql_text(' || i || ') = ' || l_sql_text(i) );
end loop;
end if;
end;
/