UTL_MAIL : Email from PL/SQL in the Oracle Database
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.
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.
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 => 'firstname.lastname@example.org', recipients => 'email@example.com,firstname.lastname@example.org', cc => 'email@example.com', bcc => 'firstname.lastname@example.org', subject => 'UTL_MAIL Test', message => 'If you get this message it worked!'); END; /
Send Emails with Attachments
The package also supports sending mails with
VARCHAR2 attachments using the
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 be
RAWdepending 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 => 'email@example.com', recipients => 'firstname.lastname@example.org,email@example.com', cc => 'firstname.lastname@example.org', bcc => 'email@example.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...