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.