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