Save information about all errors in an after-servererror on database : Database Level Trigger « Trigger « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Trigger
3. Database Level Trigger

SQL> create table userLog
  2  (error_date date,
  3   username varchar2(30),
  4   error_msg varchar2(2000),
  5   error_sql varchar2(2000));

Table created.

SQL>
SQL> create or replace trigger log_errors after servererror on database
  2  declare
  3     sql_text ora_name_list_t;
  4     msg varchar2(2000) := null;
  5     stmt varchar2(2000):= null;
  6  begin
  7     for i in 1 .. ora_server_error_depth loop
  8        msg := msg||ora_server_error_msg(i);
  9     end loop;
 10     for i in 1..ora_sql_txt(sql_text) loop
 11        stmt := stmt||sql_text(i);
 12     end loop;
 13     insert into userLog(error_date,username,error_msg,error_sql)values (sysdate,ora_login_user,msg,stmt);
 14  end;
 15  /

Trigger created.

SQL>
SQL>
SQL>
SQL> drop table userLog;

Table dropped.