Code Samples

A Database Error-Logging Trigger

This is the source for a trigger which will log every error which the database encounters to the BMC_DEBUG_LOG table.

The user which creates this trigger must have the CREATE TRIGGER and ADMINISTER DATABASE TRIGGER privileges, and must have been specifically granted EXECUTE on the BMC_DEBUG package.

You will need to change the trigger body so that the call to BMC_DEBUG.MSG names the schema in which the BMC_DEBUG package is installed (here it is DBG_OWNER).

-- BMC_DEBUG based database error logging trigger

-- To create this you must have CREATE TRIGGER and ADMINISTER DATABASE TRIGGER privilege

-- You must also have EXECUTE privilege on the BMC_DEBUG package granted directly

--

-- To list SERVERERROR triggers :-

-- select owner,trigger_name,trigger_type,triggering_event,action_type,when_clause

-- from dba_triggers where triggering_event like 'ERROR%';

CREATE OR REPLACE TRIGGER bmc_debug_log_server_errors

AFTER SERVERERROR ON DATABASE DECLARE

sql_text ora_name_list_t;

stmt CLOB;

stmt2 VARCHAR2(1000);

n NUMBER;

errno NUMBER;

this_stack VARCHAR2(1000) := SUBSTR(DBMS_UTILITY.FORMAT_ERROR_STACK,1,1000);

BEGIN

-- Get the triggering ORA- error number

errno := ora_server_error(1);

-- We do not log the following errors :-

-- ORA-25228 timeout or end-of-fetch during message dequeue

-- ORA-24034 application is already a subscriber for queue

IF errno IN (25228,24034) THEN

RETURN;

END IF;

-- Return the triggering SQL statement (n = number of items in the returned array)

-- NOTE - Due to bug 14362485 ora_sql_txt is broken in 11.2.0.3 and does not return the SQL text

-- for DDL or if a statement has been rewritten by cursor sharing :(

n := ora_sql_txt(sql_text);

IF n > 100 THEN

n:= 100;

END IF;

-- Reconstruct the triggering SQL statement

BEGIN

FOR i IN 1..n LOOP

stmt := stmt || sql_text(i);

END LOOP;

stmt2 := dbms_lob.substr(stmt,1000,1);

EXCEPTION

WHEN VALUE_ERROR THEN

stmt2 := 'Could not find SQL';

END;

-- Log the error to the bmc_debug_log table only

dbg_owner.bmc_debug.msg(msglevel => 0,

msg => translate(this_stack||'- SQL='||stmt2,chr(10)||chr(13),' '),

flag => 'SRV',

force_package => 'TRIGGER',

force_procedure => 'AFTER SERVERERROR ON DATABASE',

write_to_msg_store => FALSE,

write_to_app_info => FALSE,

write_to_dbms_output => FALSE);

EXCEPTION

WHEN OTHERS THEN

NULL;

END bmc_debug_log_server_errors;

/

Trigger created.

Once the trigger has been created, you can test it by running a statement which generates an error from any database user e.g. :-

SQL> SELECT * FROM zzz;

SELECT * FROM zzz

*

ERROR at line 1: ORA-00942: table or view does not exist

If you then look in the BMC_DEBUG_LOG table, you will see the error has been captured, along with the statement which caused it :-

SQL> SELECT log_date,sid,serial#,package_name,procedure_name,msg_level,flag,msg FROM bmc_debug_log

WHERE package_name = 'TRIGGER' AND msg LIKE 'ORA%';

LOG_DATE SID SERIAL# PACKAGE_NAME PROCEDURE_NAME MSG_LEVEL FLA MSG

----------------------------- ----- ------- -------------- ------------------------------ --------- --- -----------------------------------------------------------------

02-NOV-2014 14:34:32.459035 1796 47427 TRIGGER AFTER SERVERERROR ON DATABASE 0 SRV ORA-00942: table or view does not exist - SQL=SELECT * FROM zzz

If you find that the trigger is recording certain errors very frequently and you'd like it to ignore them, you can include the error numbers in the IN clause in the first IF statement.