UTL_SMTP: E-Mail from PL/SQL

By: Darryl Hurley, Mobile Data Solutions, Inc.

Sending electronic mail from PL/SQL is a long awaited feature that finally arrived with version 8.1 of the database. Finally the outside world can be notified directly of events within the database using standard PL/SQL packages. Things like notifying users of important data changes or notifying the DBA of impending lack of storage space can now be performed within PL/SQL.

Granted that sending mail from the database has been available for quite some time using methods like creating text files of mail commands for CRON jobs to process later or similar interfaces but now it can be done directly from PL/SQL! Well, maybe not directly; there are several interfaces used along the way as shown in Figure 1.

Figure 1 : Interfaces between PL/SQL and the Mail Server

The main PL/SQL package called UTL_SMTP derives its name from the Simple Mail Transfer Protocol (SMTP) which is the external protocol it mimics within the database. Underlying the UTL_SMTP package is the UTL_TCP package, which mimics the external Transmission Protocol/Internet Protocol (TCP/IP). The interaction of the two PL/SQL packages is a mirror of the two external protocols they mimic in that one provides a specific interface for the other.

• TCP/IP transfers data of all sorts over networks between computers; the SMTP protocol offers up a specific set of commands to send and receive e-mail via TCP/IP.
  

• The UTL_TCP package transfers data back and forth between the database and the outside world via TCP/IP; the UTL_SMTP package provides a set of e-mail specific commands on top of the UTL_TCP package.

Right in the middle of this entire interaction is Oracle’s Jserver in the database. The infrastructure relies on Java to be intermediary between the database and the TCP/IP, SMTP facilities.

This document makes no attempt to explain the inner workings of Jserver, Java, TCP/IP or SMTP it focuses strictly on the UTL_SMTP and UTL_TCP package and their interaction to send e-mail from PL/SQL.

Prerequisites

In order to use PL/SQL to send e-mail there are a few things the must be installed first.

TCP/IP
Since SMTP relies on TCP/IP it naturally follows that UTL_SMTP require it as well. Thus this package can only be used on TCP/IP networks.

Oracle Jserver
In order to use the UTL_SMTP package the Jserver option of Oracle must be installed because several Java functions and procedures are called from UTL_SMTP and UTL_TCP.

SMTP
The SMTP must be installed and accessible from the machine where the database is installed.

A Simple Example

Let’s begin with a simple example (only ten lines of code) to demonstrate just how easy it is to send mail from PL/SQL.

 SQL> DECLARE
  2    v_connection UTL_SMTP.CONNECTION;
  3  BEGIN
  4    v_connection := UTL_SMTP.OPEN_CONNECTION('mailhost.bc.ca',25);
  5    UTL_SMTP.HELO(v_connection,'mailhost.bc.ca');
  6    UTL_SMTP.MAIL(v_connection,'me@here.bc.ca');
  7    UTL_SMTP.RCPT(v_connection,'you@there.bc.ca');
  8    UTL_SMTP.DATA(v_connection,'Sent From PL/SQL');
  9    UTL_SMTP.QUIT(v_connection);
 10  END;
 11  /

Soon after running the above code I receive the nondescript offering in my In-Box.

Opening the message reveals that this message has indeed come from PL/SQL.

This example is lacking several important components of a basic e-mail message like sender name, and subject. Lets take step back and look at things from the perspective of SMTP.

Simple Mail Transfer Protocol (SMTP)

SMTP contains a series of commands for interacting with a mail server. For example the RCPT command is used to identify the recipient of an e-mail and the MAIL command actually sends an e-mail. Each of these commands relies on a connection established via TCP/IP to the mail server.

The basic steps in sending an e-mail using SMTP are as follows :

1. Begin a transaction with the mail server using the MAIL command.
   

2. Specify recipients of the mail using the RCPT command.
   

3. Providing the text or data of the message using the DATA command. An end-of-mail indicator within the data denotes the end of the transaction.

Under the covers SMTP is making use of TCP/IP to establish the connection and transfer data back and forth. This is synonymous with the UTL_SMTP package relying on the UTL_TCP package.

Each SMTP call returns a reply code indicating either success or specific reason for failure.

Now that we have and overview of how SMTP works let’s examine its counterpart within the database, UTL_SMTP.

UTL_SMTP Package

The UTL_SMTP package provides a series of PL/SQL procedures and functions on top of SMTP commands responsible for sending mail. The steps described above to send an e-mail using SMTP would look like this in UTL_SMTP.

1. Establish a connection to the mail server using the OPEN_CONECTION, HELO and MAIL procedures or functions.
   
2. Specify recipients using the RCPT procedure or function.
   
3. Provide the text or data of the message using the DATA procedure or function and close the connection with the QUIT procedure or function.

Notice that in each case it is possible to use either a procedure or function. The procedure simply executes the operation while the function executes the operation and returns a reply from SMTP.

As eluded to earlier the UTL_SMTP package is making calls to the UTL_TCP package to establish the connection and transfer data back and forth.

Let’s examine each operation used to send the e-mail and it’s invocation within PL/SQL in detail.

Opening a Connection

Connecting to the mail server is done using the aptly named OPEN_CONNECTION function, which has two arguments; host and port. Host is the name of the mail host and port is the port the mail host uses (usually 25). The functions return is of type CONNECTION.

An example of the OPEN_CONNECTION function.

DECLARE
  v_connection UTL_SMTP.CONNECTION;
BEGIN
  v_connection := UTL_SMTP.OPEN_CONNECTION('mailhost.bc.ca',25);

 The returned connection is then used as an argument for future UTL_SMTP operations.

Identifying the Sender

The first step after establishing a connection is to identify the sender to the SMTP server and initialize state with the HELO procedure or function. Here’s two examples; one for the procedure and one for the function.

UTL_SMTP.HELO(v_connection,'mailhost.bc.ca');
v_reply := UTL_SMTP.HELO(v_connection,'mailhost.bc.ca');

The return value is typed as UTL_SMTP.REPLY. It’s good practice to check the return code from calls to HELO to diagnose problems. 

Initiate the Mail Transaction

Once the connection has been opened and the domain of the sender has been identified its time to begin the mail transaction. First in line for that operation is the MAIL procedure/function which identifies the user sending the message. This one is also callable as a procedure or a function.

UTL_SMTP.MAIL(v_connection,'me@here.bc.ca');
v_reply := UTL_SMTP.MAIL(v_connection,'me@here.bc.ca');

The return value is typed as UTL_SMTP.REPLY. It’s good practice to check the return code from calls to HELO to diagnose problems.

Identify the Recipient

The next thing to identify is the mailbox to receive the e-mail. This is done using the RCPT procedure or function.

UTL_SMTP.RCPT(v_connection,'you@there.bc.ca');
v_reply := UTL_SMTP.RCPT(v_connection,'you@there.bc.ca');

The return value is typed as UTL_SMTP.REPLY. It’s good practice to check the return code from calls to RCPT to diagnose problems.

Provide Content

Next up we need to provide some content for the e-mail because nobody likes receiving blank ones. There are several procedures/functions to do this. Let’s use the simplest version, DATA for our example. As is the case with most of the code in the UTL_SMTP package this is available as both a procedure and a function.

UTL_SMTP.DATA(v_connection,'Sent From PL/SQL');
v_reply := UTL_SMTP.DATA(v_connection,'Sent From PL/SQL');

Note that DATA also sends the mail.

The return value is typed as UTL_SMTP.REPLY . It’s good practice to check the return code from calls to DATA to diagnose problems.

Close the Connection

The final step  in the operation is to close the connection to the SMTP server with the QUIT procedure/function.

UTL_SMTP.QUIT(v_connection);
v_reply := UTL_SMTP.QUIT(v_connection);

The return value is typed as UTL_SMTP.REPLY. It’s good practice to check the return code from calls to QUIT to diagnose problems.

Putting It All Together

All of the above commands come together as shown here to send electronic mail from PL/SQL.

SQL> DECLARE
  2 
  3    v_connection UTL_SMTP.CONNECTION;
  4    v_reply      UTL_SMTP.REPLY;
  5 
  6  BEGIN
  7 
  8    v_connection := UTL_SMTP.OPEN_CONNECTION('mailhost.bc.ca',25);
  9    v_reply := UTL_SMTP.HELO(v_connection,'mailhost.bc.ca');
 10    v_reply := UTL_SMTP.MAIL(v_connection,'me@here.bc.ca');
 11    v_reply := UTL_SMTP.RCPT(v_connection,'you@there.bc.ca');
 12    v_reply := UTL_SMTP.DATA(v_connection,'Sent From PL/SQL');
 13    v_reply := UTL_SMTP.QUIT(v_connection);
 14 
 15  END;
 16  /

PL/SQL procedure successfully completed.

This is just the simplest of examples and is missing many important components of an e-mail message. It only scratches the surface of what can be done in the arena of TCP/IP, SMTP and PL/SQL.