Remote DBA Plans
Remote DBA Service

Oracle UTL_SMTP to Send Email
Oracle Tips by Burleson Consulting

Advanced Oracle Utilities: The Definitive Reference by Rampant TechPress is written by the top Oracle database experts (Bert Scalzo, Donald Burleson, and Steve Callan). The following is an excerpt from the book.

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.

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)

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.

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