Log All Database Errors To A Table

Most database errors can be catched by a trigger "after servererror" on database level. The following code is an example of how the information can be logged to a database table.

create table log_servererror
(ts              timestamp
,login_user      varchar2(30)
,current_schema  varchar2(30)
,message         varchar2(2000)
,sqltext         varchar2(2000)
);

--/
create or replace trigger log_servererror
  after servererror on database
declare
  lv_sqltext_list ora_name_list_t;
  lv_message      varchar2(2000) := null;
  lv_sqltext      varchar2(2000) := null;
begin
  -- get all the error message text
  for err_depth in 1 .. ora_server_error_depth
  loop
    lv_message := lv_message || ora_server_error_msg(err_depth);
  end loop;
  -- get the sql statement
  for i in 1 .. ora_sql_txt(lv_sqltext_list)
  loop
    lv_sqltext := lv_sqltext || lv_sqltext_list(i);
  end loop;
  -- insert into our log table
  insert into log_servererror
  (  ts
  ,  login_user
  ,  current_schema
  ,  message
  ,  sqltext
  ) values
  (  systimestamp
  ,  ora_login_user
  ,  sys_context('userenv','current_schema')
  ,  lv_message
  ,  lv_sqltext
  );
end;
/

-- force an error
select *
from   table_does_not_exist
;

-- see what has been logged
select *
from   log_servererror
;

-- cleanup
--drop trigger log_servererror;
--drop table   log_servererror;