René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

Servererror trigger [Oracle]

The servererror system event trigger is fired when an error is encountered.
This trigger is not fired, however, for the ORA-01034 ORACLE not available, ORA-01403 no data found, ORA-01422 exact fetch returns more than requested number of rows, ORA-01423 error encountered while checking for extra rows in exact fecht and ORA-04030 out of process memory when trying to allocate string bytes.
If an error occurs and the trigger is fired, but the trigger cannot be executed successfully, the original error is prepended with a ORA-04045 error during recompilation/revalidation of %s%s.

An example

A table is created that will then be filled by the trigger when an error has occured. 
create table caught_errors (
  dt        date,               
  username  varchar2( 30), -- value from ora_login_user
  msg       varchar2(512),
  stmt      varchar2(512)
);
This trigger will actually fill the table: 
create or replace trigger catch_errors
   after servererror on database
declare
   sql_text ora_name_list_t;
   msg_     varchar2(2000) := null;
   stmt_    varchar2(2000) := null;
begin

  for depth in 1 .. ora_server_error_depth loop
    msg_ := msg_ || ora_server_error_msg(depth);
  end loop;

  for i in 1 .. ora_sql_txt(sql_text) loop
     stmt_ := stmt_ || sql_text(i);
  end loop;

  insert into 
    caught_errors (dt     , username      ,msg ,stmt )
           values (sysdate, ora_login_user,msg_,stmt_);
end;
/
Now, a select statement is executed on a table that does not exist: 
SQL> select * from foijf;
select * from foijf
              *
ERROR at line 1:
ORA-00942: table or view does not exist
This error is now reported in caught_errors: 
SQL> set lines 100
SQL> select dt, username, substr(stmt, 1, 40) from caught_errors;

DT        USERNAME                       SUBSTR(STMT,1,40)
--------- ------------------------------ ----------------------------------------
25-MAR-05 RENE                           select * from foijf