 |
|
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. |