Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 
 

Oracle utl_mail e-mail PL/SQL package

March 1, 2015

Oracle Tips by Burleson Consulting

Oracle Database 10g has made it easier than ever before to interface PL/SQL with e-mail.

UPDATE:  See these other important notes on sending e-mail from Oracle.

The utl_mail package makes it easy for a PL/SQL programmer to send e-mail from Oracle.  The utl_mail package is different from the utl_smtp package because you don't need to understand the internal machinations of the SMTP protocol.

This, and other new PL/SQL features are fully described in Dr. Hall's book. "Oracle job scheduling".

The utl_mail utility is also described in the book "Oracle10g New Features".  According to the 10g new features book, utl_mail interfaces with the smtp_out_server parameter to specify the outbound destination for e-mail:

"smtp_out_server  specifies the SMTP host and port to which utl_mail delivers outbound E-mail. Multiple servers may be specified, separated by commas. 

If the first server in the list is unavailable, then utl_mail tries the second server, and so on."

Here is a PL/SQL example of using utl_mail to send an e-mail message:

Here is a simple example of how to send email using utl_mail. Replace the obvious text prompts for real data. If one does not know one?s mail server, send an email to oneself and look at the header data, or ask the system administrator.

 

ALTER SYSTEM SET smtp_out_server = 'mailserver.domain.com';

DECLARE

vSender VARCHAR2(30) := 'sender@somewhere.com';

vRecip VARCHAR2(30) := 'your.name@domain.com';

vSubj VARCHAR2(50) := 'Enter the subject here';

vMesg VARCHAR2(4000) := 'Enter the body';

vMType VARCHAR2(30) := 'text/plain; charset=us-ascii';

BEGIN

utl_mail.send

(vSender, vRecip, NULL, NULL, vSubj, vMesg, vMType, NULL);

END;

/

More utl_mail tips:

The utl_mailpackage 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_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.

 

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

 

<  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

 ===================================================

UTL_MAIL

This package is a simple utility for sending emails with features like attaching at a maximum of one file per email, carbon copy (CC), and blind carbon copy (BCC) and was introduced in the Oracle version 10g to be the replacement for its clumsy predecessor, the UTL_SMTP package.

 

% Note: This package is capable of only sending emails and cannot receive any!


This package requires the SMTP_OUT_SERVER initialization parameter to be explicitly set to the UTL_MAIL’s out-bound email’s SMTP host and port in the init.ora file. We can set multiple SMTP servers in the SMTP_OUT_SERVER initialization parameter separated by commas. If the first server from the list is not available, the second one will be used and so on. If this parameter is not set, the host will default to the DB_DOMAIN value and the port will be defaulted to 25.

 

Due to the security concern in the SMTP_OUT_SERVER parameter setup, the UTL_MAIL package does not come installed with our database and we have to take care of that. The scripts required for installing the UTL_MAIL package are available in the below location in our Oracle home directory.

 

ORACLE_HOME/RDBMS/ADMIN/utlmail.sql

ORACLE_HOME/RDBMS/ADMIN/prvtmail.plb

 

The UTLMAIL.SQL script contains the package specification and the PRVTMAIL.PBL script contains the package body.

 

After executing the above scripts, the UTL_MAIL package will be available for general use after which the SMTP_OUT_SERVER parameter has to be configured. This can be done by performing an ALTER statement at the system level as shown below,

 

ALTER SYSTEM SET SMTP_OUTP_SERVER='mail.companyx.com, mail.companyy.com' SCOPE=BOTH;

 

In the above example, if the COMPANYX server is unavailable, the COMPANYY server will pitch in.

 

The UTL_MAIL package internally uses the UTL_TCP and UTL_SMTP packages for sending out emails. It’s just that the complexity has been reduced within the below described three simple procedures.

SEND Procedure

The SEND procedure is a one lined easy form of the UTL_SMTP package for packaging and delivering the email messages to the SMTP server for forwarding them to the appropriate receivers.

 

The prototype of the SEND procedure is shown below,

 

UTL_MAIL.SEND (

   sender     IN VARCHAR2 CHARACTER SET ANY_CS,

   recipients IN VARCHAR2 CHARACTER SET ANY_CS,

   cc         IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,

   bcc        IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,

   subject    IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,

   message    IN VARCHAR2 CHARACTER SET ANY_CS,

   mime_type  IN VARCHAR2 DEFAULT 'text/plain; charset=us-ascii',

   priority   IN PLS_INTEGER DEFAULT 3,

   replyto    IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL);

 

·         SENDER parameter accepts the email address of the sender.

·         RECIPIENTS parameter accepts the comma separated recipient email addresses.

·         CC parameter accepts the email addresses of the CC recipients in a comma separated manner.

·         BCC parameter accepts the email addresses of the BCC recipients in a comma separated manner.

·         SUBJECT parameter accepts the subject for the email message.

·         MESSAGE parameter accepts the email’s body text.

·         MIME_TYPE parameter accepts the format of the message. It stands for Multipurpose Internet Mail Extensions.

·         PRIORITY parameter accepts the priority to be set for the email message. 1 being the highest and 5 being the lowest on the priority. The default is 3.

·         REPLYTO parameter accepts the email address to whom the sent mail should reply back.

 

In the below example, the SMTP_OUT_SERVER initialization parameter is set to the MYCOMPANY’s corporate mail server and a job application mail has been sent to the HR team of Oracle Corporation for the Oracle PL/SQL developer position using the UTL_MAIL package with an ease.

 

ALTER system SET smtp_out_server='mail.mycompany.com' scope=both;

/

 

DECLARE

  l_vc_sender     VARCHAR2(100):='adamwalker1989@ mycompany.com';

  l_vc_recipients VARCHAR2(100):='hr@oracle.com';

  l_vc_cc         VARCHAR2(100);

  l_vc_bcc        VARCHAR2(100) :='adamwalker1989@ mycompany.com';

  l_vc_subject    VARCHAR2(100) :='Reg: Job application for PL/SQL developer';

  l_vc_message    VARCHAR2(4000):='Dear HR,' ||chr(10) ||chr(10) ||'Greetings!' ||chr(10) ||'I am very interested in applying for the PL/SQL developer job posted on the Oracle magazine recently. My qualifications and experience match your specifications almost exactly...';

  l_vc_mime_type  VARCHAR2(4000):='text/plain; charset=us-ascii';

  l_pi_priority pls_integer     := 1;

  l_vc_replyto VARCHAR2(100)    :='adamwalker1989@ mycompany.com';

BEGIN

  utl_mail.send(l_vc_sender,

                l_vc_recipients,

                l_vc_cc,

                l_vc_bcc,

                l_vc_subject,

                l_vc_message,

                l_vc_mime_type,

                l_pi_priority,

                l_vc_replyto);

END;

/

SEND_ATTACH_VARCHAR2 Procedure

This package is used for sending VARCHAR2 typed attachments along with the email.

                                                    

The prototype for the SEND_ATTACH_VARCHAR2 procedure is shown below,

 

UTL_MAIL.SEND_ATTACH_VARCHAR2 (

   sender        IN VARCHAR2 CHARACTER SET ANY_CS,

   recipients    IN VARCHAR2 CHARACTER SET ANY_CS,

   cc            IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,

   bcc           IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,

    subject       IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,

   message       IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,

   mime_type     IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT 'text/plain; charset=us-ascii',

   priority      IN PLS_INTEGER DEFAULT 3,

   attachment    IN VARCHAR2 CHARACTER SET ANY_CS,

   att_inline    IN BOOLEAN DEFAULT TRUE,

   att_mime_type IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT 'text/plain; charset=us-ascii',

   att_filename  IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,

   replyto       IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL);

 

·         ATTACHMENT parameter accepts the text file to be attached in the form of a variable.

·         ATT_INLINE parameter accepts a Boolean true to make the attachment viewable inline or a Boolean false if not.

·         ATT_MIME_TYPE parameter accepts the MIME type for the attachment.

·         ATT_FILENAME parameter accepts the string filename of the attachment.

 

In the below example, the resume of the candidate has been attached to the job application email using the SEND_ATTACH_VARCHAR2 procedure. If the attachment is a file, then we must use the UTL_FILE package to read the file and send them as shown below.

 

% Note: This package is capable of sending email attachments of size <=32,767 characters only and not more.

ALTER system SET smtp_out_server='mail.mycompany.com' scope=both;

/

 

DECLARE

  l_vc_sender     VARCHAR2(100):='adamwalker1989@mycompany.com';

  l_vc_recipients VARCHAR2(100):='hr@oracle.com';

  l_vc_cc         VARCHAR2(100);

  l_vc_bcc        VARCHAR2(100) :='adamwalker1989@mycompany.com';

  l_vc_subject    VARCHAR2(100) :='Reg: Job application for PL/SQL developer';

  l_vc_message    VARCHAR2(4000):='Dear HR,' ||chr(10) ||chr(10) ||'Greetings!' ||chr(10) ||'I am very interested in applying for the PL/SQL developer job posted on the Oracle magazine recently. My qualifications and experience match your specifications almost exactly...';

  l_vc_mime_type  VARCHAR2(100):='text/plain; charset=us-ascii';

  l_pi_priority pls_integer     := 1;

  l_vc_attachment varchar2(32767):='My Resume . . . ';

  l_b_att_inline BOOLEAN:=false;

  l_vc_att_mime_type VARCHAR2(100):='text/plain; charset=us-ascii';

  l_vc_att_filename VARCHAR2(100):='Curriculum Vitae';

  l_vc_replyto VARCHAR2(100)    :='adamwalker1989@mycompany.com'; 

BEGIN

  utl_mail.send_attach_varchar2(l_vc_sender,

                l_vc_recipients,

                l_vc_cc,

                l_vc_bcc,

                l_vc_subject,

                l_vc_message,

                l_vc_mime_type,

                l_pi_priority,

                l_vc_attachment,

                l_b_att_inline,

                l_vc_att_mime_type,

                l_vc_att_filename,

                l_vc_replyto);

END;

/

SEND_ATTACH_RAW Procedure

This package is used for sending RAW typed attachments along with the email. This procedure works similar to the SEND_ATTACH_VARCHAR2 procedure, but with a RAW file for its attachment.

 

The prototype for the SEND_ATTACH_VARCHAR2 procedure is shown below,

 

UTL_MAIL.SEND_ATTACH_VARCHAR2 (

   sender        IN VARCHAR2 CHARACTER SET ANY_CS,

   recipients    IN VARCHAR2 CHARACTER SET ANY_CS,

   cc            IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,

   bcc           IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,

    subject       IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,

   message       IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,

   mime_type     IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT 'text/plain; charset=us-ascii',

   priority      IN PLS_INTEGER DEFAULT 3,

   attachment    IN RAW,

   att_inline    IN BOOLEAN DEFAULT TRUE,

   att_mime_type IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT 'text/plain; charset=us-ascii',

   att_filename  IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,

   replyto       IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL);

Summary

We have learned and mastered the important utility packages which are commonly used in our day to day programming lives in this chapter with sufficient facts and examples.

 

In the next chapter, we will be learning about configuring and working with non-Oracle databases using the Heterogeneous Services.

 

If you like Oracle tuning, see the book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.


 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

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.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational