Sending Email from Within Your Oracle Database

 By Bob Mycroft

In this article I want to talk about some functionality that came in with Oracle 8i – namely the ability to send emails directly from the database using PLSQL. This is functionality that I would have killed for when I was a developer. With the arrival of the supplied package UTL_SMTP, all this has been made simple. Sending emails from the database is straightforward, quick and easy. All you need is an 8I database with the Java Virtual Machine in place and access to an SMTP email server.

1. Overview and SMTP

This article should be of interest to both the developer and the DBA. To try and shed some light on why this might be I have thought of a couple of examples where the ability for the database itself to send emails would add value to an application or system.

Firstly, imagine a simple stock control system. With email functionality available from within the rdbms you could code triggers that would email the supplier when stock of a particular item fell below a threshold level. This would be ideal for a ‘just-in-time’ warehouse system where the levels of stock must be tightly controlled.

Next, image a database/web application where the customer is notified by email when their order has been accepted, the goods packaged and finally notified when the goods are dispatched. Further to this, the database could email customers with promotional material based upon the customer profile.

For the DBAs out there, email can be used to notify you when an important overnight job has failed, or for that matter email you at fixed points in the process so that you know how the job is progressing. It could be used to tell you when hit ratios have dropped, or if there are space issues or when there is a locking problem. The power here is that although much of this will be possible via Oracle Enterprise Manager, you can bring your own expertise in writing PLSQL procedures to bear to write your own monitors, as opposed to having to know tcl (tool control language) to write your own jobs in OEM.

None of these ideas are new and certainly I imagine that they are all used out there in the real world. However, I just wanted to pick the first three that came to mind as an example. I am sure that you can think of a dozen other situations where the need for notification of an event, or of a point in a process being reached, or contacting your clients, would make your life either easier, or would add value to your system.

2. SMTP – So what the hell is it?

Well hopefully I have whetted your appetite for what may in reality be a dry subject.  Lets get the theory out of the way quickly and move onto the code! I have deliberately not gone into too much detail with the SMTP protocol, as there is masses of information available on the Internet. What I have done though is attempted to just give an overview of the protocol, as this will help later when we look at the UTL_SMTP package.

SMTP is an acronym for Simple Mail Transfer Protocol and has been around since about 1982. The RFC 821(Request for Comment) states that “The objective of Simple Mail Transfer Protocol (SMTP) is to transfer mail reliably and efficiently. “. Hence SMTP is simply a protocol for sending email across networks (such as the Internet). The protocol itself is an ASCII protocol which uses TCP port 25 to communicate between the client and the server.

Most of the email systems that send email via the Internet will be using SMTP. Many of you out there probably use SMTP without even realising it. If you have ever used mailx in Unix to send emails that you certainly have used SMTP.

 
Figure 1.1 A typical SMTP mail system showing the client and the server.

2. 1 The email structure

An email message consists of three distinct parts: The envelope, the header and the body.

Each of these three parts of an email message are required for a complete email message.

2. 2 The steps in sending an email

SMTP is implemented with a basic set of commands that I will cover later. Extended protocols do exist but the commands used by UTL_SMTP package uses the basics that are used everywhere.

At its simplest level, the client ‘handshakes’ with the server and initiates an SMTP session. For each command the client sends, the server sends a reply. The reply is a 3 digit code with text that tells the client if the command was successful or if it had failed. When the server has replied then the client send information to identify the originator, to identify the recipient(s), and the actual message. After each command a reply is sent back. Assuming all goes well, the session is concluded and the email is sent. One important thing to note is that SMTP requires <CR>+<LF> (carriage return and linefeed) to terminate a line.

So the email session is as follows

2.3 SMTP commands

Very briefly then the commands are as follows: This is not a complete listing but covers most of what you will need. For a complete listing refer to the RFCs

2.4 SMTP Replies

A reply is simply the way that the SMTP server tells you that a command was successful. The codes are numeric 3 digit numbers. In general the codes fall into broad bands as follows: Numbers in the 2-300 range are used for information – ie 250 is the ‘OK’ reply code. Codes in the 400 range are more permanent errors – for example mailbox unavailable. Reply codes in the 500 range are more to do with syntax – for example 502 is ‘command not implemented’, 503 is ‘bad sequence of commands’. For a full listing of the reply codes and what they mean, look at chapter 65 of the “Supplied PLSQL Packages Reference”.

3. The UTL_SMTP Package and some sample code.

3.1 Prerequisites

Before you can use the package UTL_SMTP there are some prerequisites that need to be in place.  The database that you want to use for email has to be configured first. The UTL_SMTP package is installed into the database by default on database creation but in order for the package to work you will need to install the Java Virtual Machine into the database. An excellent guide on installing the Java Virtual Machine can be found in “Note :105472.1  Setup, Configuration, and Use of the Java Virtual Machine” on metalink.oracle.com . In essence, there are 3 scripts you need to run to install the JVM. These are:

$ORACLE_HOME\javavm\install\initjvm.sql,

$ORACLE_HOME\javavm\install\init_jis.sql and

$ORACLE_HOME\javavm\install\initplsj.sql

3.2 The  UTL_SMTP package.

Within the UTL_SMTP package, the commands are represented as both functions and procedures. In the code I have used the procedures. A more tightly controlled example might capture return codes and use them in error reporting. Instead I have prompted for using the supplied exception handlers. I am not covering every procedure/function that is included in the package as there are many and you will probably never need most of them. What I have tried to do is single out the ones that you will use for the basic implementation, and explain how to put them together.

TYPE CONNECTION: A CONNECTION is a record containing port and host information. You will use the connection object in all the other procedures but will never have to look at the contents of the connection object returned by the server.

TYPE REPLY, TYPE REPLIES: A REPLY is a PLSQL record representing an SMTP reply. It is made up of the reply code and the reply text. A REPLIES record is a PLSQL table of reply records.

UTL_SMTP.OPEN_CONNECTION(): This is only a function only and returns an record of type CONNECTION. This is always the first thing you will do in an SMTP transaction.

UTL_SMTP.HELO(connection IN, domain IN): This is the Handshake procedure. Pass in the connection object from earlier and a string that identifies the sending host. This is overloaded as a function which returns a REPLY. HELO is always the next thing called after an OPEN_CONNECTION()

UTL_SMTP.MAIL(connection IN, sender IN, parameters IN OUT): MAIL initiates the SMTP transaction. Sender is the email address of the sender, parameters is used for extended SMTP as per RFC1869.

UTL_SMTP.RCPT(connection IN, recipient IN, parameters IN OUT): RCPT specifies the recipients of the email. Recipient is the email address of the recipient. You can call this procedure for every recipient of the message. You must call MAIL before RCPT so that the SMTP transaction has started. Again, RCPT is overloaded as a function.

UTL_SMTP.OPEN_DATA(connection IN): Sends the DATA command. All subsequent calls to UTL_SMTP.WRITE_DATA(connection IN, data IN): adds data to the string to be sent and finally UTL_SMTP.CLOSE_DATA(connection IN) ends the message by sending a period enclosed in <CR><LF>.  WRITE_DATA may be called as many times as you wish. An alternative to the above is a single call to UTL_SMTP.DATA(). There is no function version of WRITE_DATA as the text isnt sent until the CLOSE_DATA is sent.

UTL_SMTP.QUIT(connection IN): This closes the transaction.

One other procedure that you may use is UTL_SMTP.COMMAND() This allows you to send generic SMTP commands. If there are SMTP commands that do not have an equivalent in the UTL_SMTP package then use this procedure.

One further thing is that as <CR><LF> is used to terminate lines, it is a good idea to define this early in the code. In the UTL_TCP package there is a constant called UTL_TCP.CRLF already defined.

There are some limitations to the package though. Firstly you cannot send attachments with the email. Also all text data is converted to US7ASCII before being sent. Later SMTP extensions (8BITMIME – RFC1652) allow 8-bit communication. This has to be negotiated with the EHLO() command – an extension of HELO().

3.3 An Example Procedure

In my example procedure I have split out the header from the body of the email and posted the two parts separately using WRITE_DATA. This is just done for personal preference as the whole lot could have been submitted via a single call to DATA. Note how the string for header is formatted to include To:. From: etc.

4. In Conclusion

Hopefully this article will have given you an appetite to give it a go. Sending emails from the database is such a useful piece of functionality that I’m sure you will all be thinking of uses for it. For more information on SMTP and UTL_SMTP I would suggest that you look at the Supplied PLSQL Packages’ manual and search the web for the relevant RFCs.

Bob Mycroft, Oracle DBA

Bmycroft@yahoo.com