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.
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.
Daily Tech Insider Newsletter
Stay up to date on the latest in technology with Daily Tech Insider. We bring you news on industry-leading companies, products, and people, as well as highlighted articles, downloads, and top resources. You’ll receive primers on hot tech topics that will help you stay ahead of the game.
Delivered Weekdays
