O'Reilly logo

Oracle PL/SQL Programming, 5th Edition by Bill Pribyl, Steven Feuerstein

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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     => 'me@mydomain.com'
     ,recipients => 'you@yourdomain.com'
     ,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 => 'you@yourdomain.com, him@hisdomain.com'

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required