Sending e-mail from within Oracle
October 21, 2004
Using e-mail from within Oracle can be a great advantage. Many methods of sending e-mail extract the information and then package it up outside of Oracle. However, Oracle has supplied us with all that we need to do everything from within Oracle. This can be quite handy when we as DBAs need to check statuses, errors, alerts, or our favorite DBA report regularly. You can just invoke the mail package and have them delivered right to our in-box.
The first step in understanding how to send e-mail from within Oracle is to become familiar with the UTL_SMTP package. This is a set of stored PL/SQL procedures that allow you to interact with the Simple Mail Transfer Protocol (SMTP). The SMTP is the standard for transmitting e-mail across the internet. If you would like to read more, visit http://en.wikipedia.org/wiki/Simple_Mail_Transfer_Protocol to gain some good insights into this protocol. It is always a good place to start if you are trying to send something and either it does not send or does not arrive properly. Many times, you can see that there are limitations in the protocol that you might be exceeding, thus causing problems.
Listing 1 is the simplest you can get when interacting with this package.
The following code example illustrates how the SMTP package might be used by an application to send e-mail. The application connects to an SMTP server at port 25 and sends a simple text message. One of the first questions asked after getting into sending mail from within Oracle is how to populate the standard heading information with a Subject and From / To / CC / BCC address but still be able to send text in the body of the message. This is where reading the above mentioned document standards for Simple Mail Transfer Protocol comes in handy. There, you will find that you must conform to the standard by actually spelling out these items so that SMTP will recognize them. Also within the doc, you will find out that there needs to be a separator line from the heading information and the text body. Listing 1 does all this for you.
Also within Listing 1, I have added what most other examples, (at least those that I have ever come across), do not cover--the error checking that goes along with each and every call to the UTL_SMTP package. It does not take much more effort, but gives you a wealth of information on the success or failure of your process. In addition, it is a great learning tool to actually see some of the messages coming out of the UTL_SMTP package. If you look closely at the listing, there are two interchangeable calls, one to 'helo' and the other to 'ehlo'. These two calls do the same thing but the messages coming out of the ehlo call provide much more information to what is going on behind the curtains. Also, note in the code from Listing 1, the difference between vreply and vreplies. Vreply is a single record returned from the call while vreplies contains multiple records and thus an index to sequence through the message. Listing 2 shows output that was generated to help you see what these messages look like. Obviously you could put some logic within the code in Listing 1 to branch off and do error routines if problems where encountered during the sending of mail. All of the information about UTL_SMTP package calls is well documented in Oracle's PL/SQL Packages and Types References, but how to actually present and sequence through return values is not covered.
This article was written to give you an idea of how to send a simple mail message from within Oracle. There are a few good examples of how to e-mail different attachments across the web and all you need to do is a simple search. However, most of the examples I found on the web did not include how to extract the return codes and messages. I felt this was something that needed to be covered, and have given all of the different methods for message extraction in this example. Now all you need to do is incorporate a bit of logic to handle those exceptions.