Save information about all errors in an after-servererror on database

   

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.

Related examples in the same category

1.	Trigger for database startup event
2.	Trigger to log the database shutdown
3.	Logon trigger
4.	After logon database trigger
5.	Log database logon to a table
6.	Log a server error to a table
7.	Log a server error with information on time, user name, database name
8.	A system trigger(AFTER CREATE ON DATABASE) .
9.	creating a logon/logoff auditing system using system-level triggers:
10.	Submit a job for altering user in an after-create-database trigger

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

Related examples in the same category