Oracle alert log in stored procedure Oracle Tips by Burleson Consulting Don Burleson

Note: In 11g and beyond, you can directly query the alert log with SQL, using x$dbgalertext.

You can read and write to the Oracle alert log from inside an Oracle stored procedure:

1 - Locate the background dump directory (the location of the alert log).
2 - Set the utl_file_dir initialization parameter.
3 - Execute utl_file.fopen to open the file for write access.
4 - Use dbms_output.put_line to write the custom message to the alert log.
5 - Execute utl_file.fclose to close the file

-- ******************************************************
-- Gather the location of the alert log directory
-- ******************************************************

select
name into :alert_loc
from
v$parameter
where
name = ‘background_dump_destination’;

-- ******************************************************
-- Set the utl_file_dir
-- (prior to Oracle9i, you must bounce the database)
-- ******************************************************
alter system set utl_file_dir = ‘:alert_log’);


-- ******************************************************
-- Open the alert log file for write access
-- ******************************************************
utl_file.fopen(':alert_loc',’alertprod.log’,'W');

-- ******************************************************
-- Write the custom message to the alert log file
-- ******************************************************
dbms_output.put_line('invalid_application_error');

-- ******************************************************
-- Close the alert log file
-- ******************************************************
utl_file.fclose(':alert_loc');

Scripts like this  can be found in Mike Ault's script collection:

http://www.rampant-books.com/download_adv_mon_tuning.htm

---