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.