Software

Send e-mail from Oracle via the UTL_MAIL package

Oracle 10g includes the UTL_MAIL package, which eliminates much of the extra work that UTL_SMTP required. Discover why you should appreciate the simplicity of UTL_MAIL.

Since Oracle 8i, the database has had the capability to send Internet e-mail via the UTL_SMTP package. This supplied PL/SQL package implements the Simple Mail Transfer Protocol (SMTP) as defined by Internet Request for Comments (RFCs).

Oracle 10g includes a new package, UTL_MAIL, that eliminates much of the extra work that the earlier package required. It is an additional layer to UTL_SMTP, which is still available as well.

In order to appreciate the simplicity of UTL_MAIL, let's review the old process. The functions in UTL_SMTP mimic the steps of an e-mail session. First, the UTL_SMTP.OPEN_CONNECTION function is used to connect to an e-mail server at a specific port number. Once a successful connection is established, multiple functions are called in a specific order to communicate with the mail server. The UTL_SMTP.HELO procedure begins a mail session by sending the HELO ("hello") message to the server. This is followed by UTL_STMP.MAIL to identify the sender mailbox, UTL_SMTP.RCPT to identify the recipient, and UTL_SMTP.DATA to send the text of the message. The session is terminated with UTL_SMTP.QUIT.

Using UTL_MAIL, these multiple functions collapse into a single stored procedure named UTL_MAIL.SEND. The format is:

UTL_MAIL.SEND (sender, recipientlist, cc, bcc, subject,
               Message, mime_type, priority)

The recipientlist, cc, and bcc parameters are all comma-separated lists of recipient, copy to, and blind copy e-mail addresses. The sender, subject, message, and mime_type parameters are all single item fields. All of the above are defined as VARCHAR2. The last parameter, priority, is a PLS_INTEGER that defines the message priority.

Weekly Oracle tips in your inbox
TechRepublic's free Oracle newsletter, delivered each Wednesday, covers automating Oracle utilities, generating database alerts, solving directed graph problems, and more.
Automatically sign up today!

The package also contains two procedures that deal with attachments. The SEND_ATTACH_RAW and SEND_ATTACH_VARCHAR2 procedures are similar to SEND, but with an additional parameter of either RAW or VARCHAR2 type for sending attachments.

Note: UTL_MAIL is not configured by default for security reasons. You must install it by connecting as SYS, then executing the utlmail.sql script in the $ORACLE_HOME/rdbms/admin directory. In addition, you must configure an init parameter, SMTP_OUT_SERVER, to point to an outgoing SMTP server (unlike UTL_STMP, this is not specified in the function arguments and must be pre-defined).

Miss a tip?

Check out the Oracle archive, and catch up on our most recent Oracle tips.

Bob Watkins (OCP, MCDBA, MCSE, MCT) is a computer professional with 25 years of experience as a technical trainer, consultant, and database administrator. Visit Bob's site.

3 comments
suhas_dwar
suhas_dwar

We need to run @$ORACLE_HOME/rdbms/admin/prvtmail.plb as well (it is the utl_mail package body, wrapped)

hal.zimmerman
hal.zimmerman

Left out that, to creat the package body, also execute \%oracle_home%\rdbms\admin\prvtmai.plb then grant execute on utl_mail to public (or user). Metalink Note:269375.1 is very good on this. Great, though. I did not know this package existed until this article. And now with file attachments! Hal

marv732
marv732

the demo_mail package which has it all ready for you. I've used it a few times in production systems and found it very handy. Greg

Editor's Picks