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   => 'AUTOMATED_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.