Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 

 

Oracle Scheduling Sending Email Notifications of Job Errors

Oracle Tips by Burleson Consulting

Sending Email Notifications of Job Errors

The mechanism for sending email notifications can vary depending on the version of Oracle being used.  Oracle10g allows the use of the simpler utl_mail package rather than the utl_smtp package available in previous versions.

Using UTL_SMTP

The utl_smtp package was introduced in Oracle8i to give access to the SMTP protocol from PL/SQL.  The package is dependant on the JServer option, which can be loaded using the Database Configuration Assistant (DBCA) or by running the following scripts as the SYS user if it is not already present.

CONN sys/password AS SYSDBA
@$ORACLE_HOME/javavm/install/initjvm.sql
@$ORACLE_HOME/rdbms/admin/initplsj.sql

Using the package to send an email requires some knowledge of the SMTP protocol, but for the purpose of this text, a simple send_mail procedure has been written that should be suitable for most error reporting.

* send_mail.sql

-- -- Parameters:
--    1) SMTP mail gateway.
--    2) From email address.
--    3) To email address.
--    4) Subject of email.
--    5) Text body of email.
-- *****************************************************************

CREATE OR REPLACE PROCEDURE send_mail (
  p_mail_host  IN  VARCHAR2,
  p_from       IN  VARCHAR2,
  p_to         IN  VARCHAR2,
  p_subject    IN  VARCHAR2,
  p_message    IN  VARCHAR2)
AS
  l_mail_conn   UTL_SMTP.connection;
BEGIN
  l_mail_conn := UTL_SMTP.open_connection(p_mail_host, 25);
  UTL_SMTP.helo(l_mail_conn, p_mail_host);
  UTL_SMTP.mail(l_mail_conn, p_from);
  UTL_SMTP.rcpt(l_mail_conn, p_to);

  UTL_SMTP.open_data(l_mail_conn);

  UTL_SMTP.write_data(l_mail_conn, 'Date: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || Chr(13));
 
UTL_SMTP.write_data(l_mail_conn, 'From: ' || p_from || Chr(13));
  UTL_SMTP.write_data(l_mail_conn, 'Subject: ' || p_subject || Chr(13));
  UTL_SMTP.write_data(l_mail_conn, 'To: ' || p_to || Chr(13));
  UTL_SMTP.write_data(l_mail_conn, '' || Chr(13));
  UTL_SMTP.write_data(l_mail_conn, p_message || Chr(13));

  UTL_SMTP.close_data(l_mail_conn);
  UTL_SMTP.quit(l_mail_conn);
END send_mail;
/
SHOW ERRORS

The following code shows how the send_mail procedure can be used to send an email.  Obviously, one will need to substitute the appropriate parameter values.

BEGIN
  send_mail(p_mail_host => 'smtp.mycompany.com',
            p_from      => 'me@mycompany.com',
            p_to        => 'you@mycompany.com',
            p_subject   => 'Test SEND_MAIL Procedure',
            p_message   => 'If you are reading this it worked!');
END;
/

The p_mail_host parameter specifies the SMTP gateway that actually sends the message.

Now that the email mechanism has been presented, how to capture errors and produce email notifications will be explained.

The simplest way to achieve this is to place all the code related to the job into a database procedure or preferably, a packaged procedure.  This allows the capture of errors using an exception handler and the generation of an appropriate email.  As an example, assume there is a need for a procedure to gather database statistics for an Oracle 8i or 9i instance.  A procedure like the one below might be defined.

* automated_email_alert.sql

-- *************************************************
-- Copyright ? 2005 by Rampant TechPress
-- This script is free for non-commercial purposes
-- with no warranties.  Use at your own risk.
--
-- To license this script for a commercial purpose,
-- contact info@rampant.cc
-- *************************************************

CREATE OR REPLACE PROCEDURE automated_email_alert AS
 
l_mail_host  VARCHAR2(50) := 'smtp.mycompany.com';
  l_from       VARCHAR2(50) := 'jobs@mycompany.com';
  l_to         VARCHAR2(50) := 'tim@mycompany.com';
BEGIN
  DBMS_STATS.gather_database_stats(cascade => TRUE,
                                   options => 'GATHER AUTO');
  send_mail(p_mail_host => l_mail_host,
            p_from      => l_from,
            p_to        => l_to,
            p_subject   => 'AUTOMATED_EMAIL_ALERT (MYSID): Success',
            p_message   => 'AUTOM
ATED_EMAIL_ALERT (MYSID) completed successfully!');

EXCEPTION
  WHEN OTHERS THEN
    send_mail(p_mail_host => l_mail_host,
              p_from      => l_from,
              p_to        => l_to,
              p_subject   => 'AUTOMATED_EMAIL_ALERT (MYSID): Error',
              p_message   => 'AUTOMATED_EMAIL_ALERT (MYSID) failed with the following
error:' || SQLERRM);
END automated_email_alert;
/
SHOW ERRORS

If this procedure were run as part of a scheduled job, an email notification would be generated whether the job completed successfully or not.  In the event of an error, the associated Oracle error would be reported.

Using UTL_MAIL in Oracle10g

Oracle10g introduced the utl_mail package, which provides a simpler and more intuitive email API.  The package is loaded by running the following scripts as the SYS user.

CONN sys/password AS SYSDBA
@$ORACLE_HOME/rdbms/admin/utlmail.sql
@$ORACLE_HOME/rdbms/admin/prvtmail.plb
GRANT EXECUTE ON UTL_MAIL TO test_user;

Before the package can be used, the SMTP gateway must be specified by setting the smtp_out_server parameter.  The parameter is dynamic, but the instance must be restarted before an email can be sent.

CONN sys/password AS SYSDBA
ALTER SYSTEM SET smtp_out_server='smtp.mycompany.com';
SHUTDOWN IMMEDIATE
STARTUP

With the configuration complete, it is now possible to send an email using the send procedure.

BEGIN
  UTL_MAIL.send(sender     => 'me@mycompany.com',
                recipients => 'you@mycompany.com',
                subject    => 'Test UTL_MAIL.SEND Procedure',
                message    => 'If you are reading this it worked!');
END;
/

As with the utl_smtp example, the code related to the job needs to be placed into a database procedure which captures errors using an exception handler and sends the appropriate email.  The following procedure is the Oracle10g equivalent of the one used in the utl_smtp example.

* automated_email_alert_10g.sql

-- *************************************************
-- Copyright ? 2005 by Rampant TechPress
-- This script is free for non-commercial purposes
-- with no warranties.  Use at your own risk.
--
-- To license this script for a commercial purpose,
-- contact info@rampant.cc
-- *************************************************

CREATE OR REPLACE PROCEDURE automated_email_alert_10g AS
  l_mail_host  VARCHAR2(50) := 'smtp.mycompany.com';
  l_from       VARCHAR2(50) := 'jobs@mycompany.com';
  l_to         VARCHAR2(50) := 'tim@mycompany.com';
BEGIN
  DBMS_STATS.gather_database_stats(cascade => TRUE,
                                   options => 'GATHER AUTO');

  UTL_MAIL.send(sender     => l_from,
                recipients => l_to,
                subject    => 'AUTOMATED_EMAIL_ALERT_10G (MYSID): Success',
                message    => 'AUTOMATED_EMAIL_ALERT_10G (MYSID) completed
successfully!');
EXCEPTION
  WHEN OTHERS THEN
    UTL_MAIL.send(sender     => l_from,
                  recipients => l_to,
                  subject    => 'AUTOMATED_EMAIL_ALERT_10G (MYSID): Error',

           
      message    => 'AUTOMATED_EMAIL_ALERT_10G (MYSID) failed with the
following error:' || SQLERRM);
END automated_email_alert_10g;
/
SHOW ERRORS

Next, a mechanism for running operating system commands and scripts from within PL/SQL will be introduced.

If combining these techniques with the error logging method described previously, one may wish to send additional information in the email (prefix, start and end timestamps) to help pinpoint the errors in the error_logs table.

 

This is an excerpt from the book "Oracle Job Scheduling" by Dr. Tim Hall.

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle job scheduling scripts.


 

 
��  
 
 

 
 
 
 
oracle dba poster
 

 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright ? 1996 -  2014

All rights reserved by Burleson

Oracle ? is the registered trademark of Oracle Corporation.