Learning Oracle PL/SQL

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