Call for Oracle support & training (800) 766-1884
Free Oracle Tips

Home
Corporate Oracle Training
Custom Oracle Training
Oracle New Features Training
Advanced Oracle DBA Classes
Oracle Tuning Courses
Oracle Tips & Tricks
Oracle Training Links
Oracle Training Links
Oracle Training Links

We are top for USA Oracle Training Clients

 

Free Oracle Tips


 
HTML Text AOL

Free Oracle App Server Tips


 
HTML Text

Oracle support

Oracle training

Oracle tuning

Rednecks!

Remote Oracle

Custom Oracle Training

Donald K. Burleson

Oracle Tips

Adding Custom Messages to the Oracle alert log

In Oracle, all major system messages are written to the Oracle alert log.  In order to create a single place for all Oracle system messages, the Oracle professional must develop a method for writing application messages into the alert log file.

By consolidating all Oracle messages (both system messages and application messages) database administration is greatly simplified because a central file exists for all Oracle-related alert messages.

The Oracle DBA can create a global PL/SQL stored procedure handle this interaction with the alert log file, and the global package can be called, passing the appropriate message to the procedure.  Here is an example of this type of call:

when others then

   dbms_custom.write_alert(:p_sysdate||‘ Application error’||:var1||’ encountered’);

This easy writing to the alert log is accomplished by using Oracle’s utl_file package.  The utl_file package allows Oracle SQL and PL/SQL to read and write directly from flat files on the server.

Writing custom messages to the Oracle alert log requires the following steps:

  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

Here is a code example for Oracle9i that illustrates the process:

-- ******************************************************

-- 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');

If you like Oracle tuning, you might enjoy my latest book “Oracle Tuning: The DefinitiveReference” by Rampant TechPress. (I don’t think it is right to charge a fortune for books!) and you can buy it right now at this link:

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

 

 

 
 
 

Oracle performance tuning book

 

 

Oracle performance tuning software

 
Oracle performance tuning software
 
Oracle performance Tuning 10g reference poster
 
Oracle training in Linux commands
 
Oracle training Excel
 
 
 
 
 
email BC:


Copyright © 1996 -  2014 by Burleson Inc. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.