Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation







 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.


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




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.

-- *****************************************************************



  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;


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




END send_mail;




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.



  send_mail(p_mail_host => '',

            p_from      => '',

            p_to        => '',

            p_subject   => 'Test SEND_MAIL Procedure',

            p_message   => 'If you are reading this it worked!');




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.




CREATE OR REPLACE PROCEDURE automated_email_alert AS

  l_mail_host  VARCHAR2(50) := '';

  l_from       VARCHAR2(50) := '';

  l_to         VARCHAR2(50) := '';


  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!');




    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;




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.

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.

Remote DBA Service

Oracle Tuning Book


Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software










BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.