Oracle utl_mail e-mail PL/SQL package

The mechanism for sending email notifications can vary depending on the version of Oracle being used.  Oracle 10g and higher allows the use of the simpler utl_mail package rather than the utl_smtp package available in previous versions.

Using utl_smtp

The obselete utl_smtppackage was first introduced in Oracle 8i 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.

conn sys@ora11g as sysdba

Connected to Oracle 11g Enterprise Edition Release 11.1.0.6.0

Connected as pkg

show user

User is "pkg"

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

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 instance.  A procedure like the one below might be defined.

* automated_email_alert.sql

conn sys@ora11g as sysdba

Connected to Oracle 11g Enterprise Edition Release 11.1.0.6.0

Connected as pkg

show user

User is "pkg"

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'automated_email_alert RT (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 captured and reported in the e-mail.

Another utilization for this is when there are several mission critical jobs; if for any reason the job fails, there is a need to inform the users first thing in the morning to prevent them from working with inaccurate data.  Therefore, we created a very simple job that runs at 7 am, and checked dba_jobs  to see if all other jobs completed okay. If they did not, it sends an e-mail to the senior analysts and DBA.  Now, let?s take a look at how utl_mail simplifies this process.

Using utl_mail

Oracle 10g first 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 with utl_mail.

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 Oracle 11g equivalent of the one used in the utl_smtp example.

* automated_email_alert_11g.sql

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