Blog do Marcio Portes

Tenho uma trigger AFTER SERVERERROR na qual grava, em uma tabela de log, todos os erros que ocorrem no banco de um determinado usuário.
Como eu conseguiria, de dentro da trigger, capturar o comando que acarretou determinado erro (que fez disparar a trigger), para nesta tabela de log eu ter o comando disparado e os erros ocorridos?

ops$marcio@LNX10GR2> create table
  2  log (
  3          dt      date,
  4          usr     varchar2(30),
  5          stmt    varchar2(4000)
  6  );

Table created.

ops$marcio@LNX10GR2>
ops$marcio@LNX10GR2> create or replace trigger catch_error
  2  after servererror on database
  3  declare
  4    l_text        ora_name_list_t;
  5    l_n           number;
  6    l_stmt        varchar2(4000);
  7  begin
  8    l_n := ora_sql_txt( l_text );
  9
 10    for i in 1 .. nvl(l_text.count,0)
 11    loop
 12          l_stmt := l_text(i);
 13    end loop;
 14
 15    l_stmt := l_stmt || '=> ';
 16
 17    for i in 1 .. ora_server_error_depth
 18    loop
 19          l_stmt := l_stmt || ora_server_error_msg(i);
 20    end loop;
 21
 22    insert into log values ( sysdate, user, l_stmt );
 23  end;
 24  /

Trigger created.

ops$marcio@LNX10GR2> show error
No errors.
ops$marcio@LNX10GR2>
ops$marcio@LNX10GR2> drop table x;
drop table x
           *
ERROR at line 1:
ORA-00942: table or view does not exist


ops$marcio@LNX10GR2>
ops$marcio@LNX10GR2> connect scott/tiger
Connected.
scott@LNX10GR2>
scott@LNX10GR2> select * from sys.aud$;
select * from sys.aud$
                  *
ERROR at line 1:
ORA-00942: table or view does not exist


scott@LNX10GR2>
scott@LNX10GR2> connect /
Connected.
ops$marcio@LNX10GR2>
ops$marcio@LNX10GR2> select * from log;

DT                  USR           STMT
------------------- ------------- ----------------------------------------------------------------------
12/01/2006 01:59:13 SCOTT         select * from sys.aud$ => ORA-00942: table or view does not exist

12/01/2006 01:59:13 OPS$MARCIO    drop table x => ORA-00942: table or view does not exist


2 rows selected.

Labels: how to

# posted by Marcio : 1:48 AM