UTL_MAIL : Email from PL/SQL in the Oracle Database

The UTL_MAIL package was introduced in Oracle 10g to provide a simple API to allow email to be sent from PL/SQL. In prior versions this was possible using the UTL_SMTP package (shown here), but this required knowledge of the SMTP protocol.

• Setup
• Send Emails
• Send Emails with Attachments

Related articles.

• Email From Oracle PL/SQL (UTL_SMTP)

Setup

The package is loaded by running the following scripts.

CONN sys/password AS SYSDBA
@$ORACLE_HOME/rdbms/admin/utlmail.sql
@$ORACLE_HOME/rdbms/admin/prvtmail.plb

In addition the SMTP_OUT_SERVER parameter must be set to identify the SMTP server.

CONN sys/password AS SYSDBA
ALTER SYSTEM SET smtp_out_server='smtp.domain.com' SCOPE=SPFILE;

-- Instance restart only necessary in 10gR1.
SHUTDOWN IMMEDIATE
STARTUP

Send Emails

With the configuration complete we can now send a mail using the SEND procedure. It accepts the following parameters.

• SENDER : This should be a valid email address.
• RECIPIENTS : A comma-separated list of email addresses.
• CC : An optional comma-separated list of email addresses.
• BCC : An optional comma-separated list of email addresses.
• SUBJECT : The subject line for the email.
• MESSAGE : The email body.
• MIME_TYPE : Set to 'text/plain; charset=us-ascii' by default.
• PRIORITY : (1-5) Set to 3 by default.
• REPLYTO : Introduced in 11gR2. A valid email address.

Below is an example of the usage.

BEGIN
  UTL_MAIL.send(sender     => 'me@domain.com',
                recipients => 'person1@domain.com,person2@domain.com',
                cc         => 'person3@domain.com',
                bcc        => 'myboss@domain.com',
                subject    => 'UTL_MAIL Test',
                message    => 'If you get this message it worked!');
END;
/

Send Emails with Attachments

The package also supports sending mails with RAW and VARCHAR2 attachments using the SEND_ATTACH_RAW and SEND_ATTACH_VARCHAR packages respectively. They work in a similar fashion to the SEND procedure, with a few extra parameters.

• ATTACHMENT : The contents of the attachment. This should be VARCHAR2 or RAW depending on which procedure you call.
• ATT_INLINE : Indicates if the attachment should be readable inline. Default FALSE.
• ATT_MIME_TYPE : The default is 'text/plain; charset=us-ascii'.
• ATT_FILENAME : The name for the attachment.

Below is an example of sending an email with a text attachment.

BEGIN
  UTL_MAIL.send_attach_varchar (
    sender       => 'me@domain.com',
    recipients   => 'person1@domain.com,person2@domain.com',
    cc           => 'person3@domain.com',
    bcc          => 'myboss@domain.com',
    subject      => 'UTL_MAIL Test',
    message      => 'If you get this message it worked!',
    attachment   => 'The is the contents of the attachment.',
    att_filename => 'my_attachment.txt'    
  );
END;
/

For more information see:

• UTL_MAIL
• Email From Oracle PL/SQL (UTL_SMTP)

Hope this helps. Regards Tim...