How to Install Utl_mail Package in Oracle 11g

utl_mail Tips

Oracle Database Tips by Donald BurlesonAugust 23 , 2015

The utl_mail package was created in Oracle 10g as the successor to the combersome utl_smtp package for sending e-mail. It makes use of utl_tcp and utl_smtp internally. The main purpose of the utl_mail package is to do what utl_smtp does, but in a much easier way. These two packages will be covered now using as an example the task of sending email notifications of job errors.

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

< Code 4.4 - utl_smtp_1.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"

* 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_fromin varchar2,
p_toin varchar2,
p_subjectin varchar2,
p_messagein 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.

< Code 4.5 - utl_smtp_2.sql

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

< Code 4.6 - utl_mail_1.sql

* 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

Inside the DBMS Packages

The DBMS packages form the foundation of Oracle DBA functionality. Now, Paulo Portugal writes a landmark book Advanced Oracle DBMS Packages: The Definitive Reference. This is a must-have book complete with a code depot of working examples for all of the major DBMS packages. Order directly from Rampant and save 30%.

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals. Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata? Oracle technology is changing and we strive to update our BC Oracle support information. If you find an error or have a suggestion for improving our content, we would appreciate your feedback. Just e-mail:

and include the URL for the page.