O'Reilly logo

Learning Oracle PL/SQL by Steven Feuerstein, Bill Pribyl

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

8.1. Sending Internet Email from PL/SQL

In most libraries, there is a way for a borrower to reserve a book that has been checked out by someone else. When the other person returns the book, the person who reserved it should receive notification. What a great application of sending email from PL/SQL!

Logically, sending email via the Internet is a process usually requiring four (or five, depending on how you count them) pieces of information:

  • Sender's email address

  • Recipient's email address

  • The actual content of the message

  • A subject for the message

  • The name of a mail server (should be defaulted)

So, one can easily imagine a procedure for sending email that goes something like this:

PROCEDURE send_mail (
   sender_email IN VARCHAR2,
   recipient_email IN VARCHAR2,
   message IN VARCHAR2,
   subject IN VARCHAR2,
   mailhost IN VARCHAR2 DEFAULT 'mailhost'
);

Almost unbelievably, Oracle doesn't provide such a procedure, but instead gives us a very low-level package called UTL_SMTP that we somehow need to deal with. SMTP stands for Simple Mail Transfer Protocol, the name of the standard way that Internet mail servers communicate.[1] Before looking at the guts of using it, though, let's step back to get a bigger picture.

[1] For those of you who are blessed with insatiable curiosity (or insomnia), you can read the actual contents of the SMTP standard in RFC-822, a document you can find with any Internet search engine.

Although the various protocols and underlying software involved in transmitting email ...

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