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
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.
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.