Sending Email Notifications of Job
Errors
The mechanism for sending email notifications
can vary depending on the version of Oracle
being used.
Oracle allows the use of the simpler
utl_mail
package
rather than the
utl_smtp
package available in previous versions.
Note that
utl_mail is covered in more detail
concerning its application for developers in
Chapter 9 and
utl_email and
utl_smtp are both covered in more
detail for DBAs in Chapter 6.
Using UTL_SMTP
The
utl_smtp
package was introduced in Oracle8i to give access
to the SMTP protocol from PL/SQL.
The package is dependent 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.
-- 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
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.
 |
Fo
r more details on Oracle utilities, see the book "Advanced
Oracle Utilities" by Bert Scalzo, Donald K. Burleson, and Steve Callan.
You can buy it direct from the publisher for 30% off directly from
Rampant TechPress.
|
