create table server_errors (
error_time timestamp,
username varchar2(30),
error_message varchar2(512),
sql_statement varchar2(2014)
);
-- Catch All error
create or replace trigger catch_servererrors
after servererror on database
declare
sql_text ora_name_list_t;
message varchar2(2000) := null;
statement varchar2(2000) := null;
begin
for depth in 1 .. ora_server_error_depth loop
message:= message|| ora_server_error_msg(depth);
end loop;
for i in 1 .. ora_sql_txt(sql_text) loop
statement := statement || sql_text(i);
end loop;
insert into server_errors (error_time,username,error_message,sql_statement) values (sysdate, ora_login_user,message,statement);
end;
/
-- Catch specific error (e.g: Here catching only error 942)
create or replace trigger catch_servererrors
after servererror on database
declare
sql_text ora_name_list_t;
message varchar2(2000) := null;
statement varchar2(2000) := null;
begin
if ( is_servererror(942) )
then
for depth in 1 .. ora_server_error_depth loop
message:= message|| ora_server_error_msg(depth);
end loop;
for i in 1 .. ora_sql_txt(sql_text) loop
statement := statement || sql_text(i);
end loop;
insert into server_errors (error_time,username,error_message,sql_statement) values (sysdate, ora_login_user,message,statement);
end if;
end;
/
Note: The servererror Trigger cannot fire for the following errors:
ORA-01403: no data found (this is in the Oracle docs but does not seem to be correct)
ORA-01422: exact fetch returns more than requested number of rows
ORA-01034: ORACLE not available
ORA-04030: out of process memory when trying to allocate string bytes (string, string)
ORA-01423: error encountered while checking for extra rows in exact fetch
Good to know:
In the trigger we have used function ora_login_user. This function can be used in the following events:
· Startup
· Shutdown
· Servererror