Oracle PL/SQL Programming, 5th Edition

Sending Email

Over the years, Oracle has gradually made it easier to send email from within a stored procedure. Here’s a short example:

/* Requires Oracle Database 10g or later */
BEGIN
   UTL_MAIL.send(
      sender     => '[e-mail]'
     ,recipients => '[e-mail]'
     ,subject    => 'API for sending email'
     ,message    =>
'Dear Friend:

This is not spam. It is a mail test.

Mailfully Yours,
Bill'
   );

END;

When you run this block, the database will attempt to send this message using whatever SMTP^[22] host the DBA has configured in the initialization file (see the discussion in the next section).

The header for UTL_MAIL.SEND is:

PROCEDURE send(sender     IN VARCHAR2,
               recipients IN VARCHAR2,
               cc         IN VARCHAR2 DEFAULT NULL,
               bcc        IN VARCHAR2 DEFAULT NULL,
               subject    IN VARCHAR2 DEFAULT NULL,
               message    IN VARCHAR2 DEFAULT NULL,
               mime_type  IN VARCHAR2
                             DEFAULT 'text/plain; charset=us-ascii',
               priority   IN PLS_INTEGER DEFAULT 3);

Most of the parameters are self-explanatory. One non-obvious usage hint: if you want to use more than one recipient (or cc or bcc), separate the addresses with commas, like this:

recipients => '[e-mail], [e-mail]'

Okay, so that’s pretty good if you have a recent version of Oracle, but what if you only have access to earlier versions, or what if you just want a little more control? You can still use the UTL_SMTP package, which is a little more complicated but nevertheless workable. If you want to code at an even lower level, you can use UTL_TCP, an external procedure, or a Java stored ...