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 |