8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 10g » Here

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.

Related articles.

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

I would suggest you use a mail relay on the database server, rather than connecting directly to an external mail server. The mail relay configuration can be simple, with a reference to "localhost" in the SMTP_OUT_SERVER parameter. Any complexities about connecting to your external mail server are then hidden in the mail relay configuration.

Send Emails

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

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_VARCHAR2 packages respectively. They work in a similar fashion to the SEND procedure, with a few extra parameters.

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

BEGIN
  UTL_MAIL.send_attach_varchar2 (
    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:

Hope this helps. Regards Tim...

Back to the Top.