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> drop table userLog;
Table dropped.