How to develop an after serverror trigger in Oracle?

Question

Hi! I'm trying to log all the errors in my database into a table. So as user sys i wrote the following code:

CREATE TABLE servererror_log (
    error_datetime  TIMESTAMP,
    error_user      VARCHAR2(30),
    db_name         VARCHAR2(9),
    error_stack     VARCHAR2(2000),
    captured_sql    VARCHAR2(1000));
/
CREATE OR REPLACE TRIGGER log_server_errors
AFTER SERVERERROR
ON DATABASE
DECLARE
 captured_sql VARCHAR2(1000);
BEGIN
  SELECT q.sql_text
  INTO captured_sql
  FROM gv$sql q, gv$sql_cursor c, gv$session s
  WHERE s.audsid = audsid
  AND s.prev_sql_addr = q.address
  AND q.address = c.parent_handle;

  INSERT INTO servererror_log
  (error_datetime, error_user, db_name,
   error_stack, captured_sql)
  VALUES
  (systimestamp, sys.login_user, sys.database_name,
  dbms_utility.format_error_stack, captured_sql);
END log_server_errors;

But when i force an error like trying to select from a non-existing table it doesn´t log the error in the table.

Is there any way to check that the trigger fires at all? Also, I tried creating a test table to insert there but it doesn't work either, even if a define the trigger as an autonomous transaction and commit inside the trigger.

Thanks, Joaquin

Answer 1

Try this. You do not need to query v$sql, you can get the statement using ora_sql_txt.

SQL> CREATE OR REPLACE TRIGGER log_server_errors
  2  AFTER SERVERERROR
  3  ON DATABASE
  4  DECLARE
  5  sql_text ora_name_list_t;
  6  stmt clob;
  7  n number;
  8  BEGIN
  9    n := ora_sql_txt(sql_text);
 10    if n > 1000 then n:= 1000; end if ;
 11    FOR i IN 1..n LOOP
 12      stmt := stmt || sql_text(i);
 13    END LOOP;
 14
 15
 16    INSERT INTO servererror_log
 17    (error_datetime, error_user, db_name,
 18     error_stack, captured_sql)
 19    VALUES
 20    (systimestamp, sys.login_user, sys.database_name,
 21    dbms_utility.format_error_stack, stmt);
 22    commit;
 23  END log_server_errors;
 24  /

Trigger created.

SQL> select * from c;
select * from c
              *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> select * from servererror_log;

ERROR_DATETIME
---------------------------------------------------------------------------
ERROR_USER                     DB_NAME
------------------------------ ---------
ERROR_STACK
--------------------------------------------------------------------------------
CAPTURED_SQL
--------------------------------------------------------------------------------
11-FEB-09 02.55.35.591259 PM
SYS                            TS.WORLD
ORA-00942: table or view does not exist
select * from c

Answer 2

Check the status of your trigger and/or the existence of other triggers with:

select trigger_name, status
from all_triggers
where triggering_event like 'ERROR%'

This should result into:

TRIGGER_NAME        STATUS
------------        -------
LOG_SERVER_ERRORS   ENABLED

If trigger is not enabled or another trigger fails, it probably will not work.

Answer 3

To see if the trigger is firing, add one or more lines to it like this:

DBMS_OUTPUT.PUT_LINE( 'Got this far' );

In SQLPlus, SET SERVEROUTPUT ON then execute a command to generate an error. You should get output like this:

dev> select * from aldfjh;
select * from aldfjh
              *
ERROR at line 1:
ORA-00942: table or view does not exist


ORA-00942: table or view does not exist

Got this far