8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
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.
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_VARCHAR2
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 beVARCHAR2
orRAW
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_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...