Sending e-mail from within Oracle

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 Basics

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.

Error Checking

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.

Listing 1
Simple code to send out
a text e-mail with error checking


CREATE or REPLACE PROCEDURE SimpleTextMessage IS
mailHOST VARCHAR2(64) := ‘mailserver.your.domain.com’;
mailFROM VARCHAR2(64);
mailTO VARCHAR2(64);
mailCONN utl_smtp.connection;
mailDATE VARCHAR2(20);
vreply utl_smtp.reply;
vreplies utl_smtp.replies;
i number;

BEGIN
mailFROM := ‘[email protected]’;
mailTO := ‘[email protected]’;
SELECT TO_CHAR(SYSDATE,’MM/DD/YYYY HH24:MI:SS’) INTO mailDATE FROM dual;

dbms_output.put_line(‘open_connection’);
dbms_output.put_line(‘—————‘);
vreply := utl_smtp.open_connection(mailHOST, 25, mailCONN);
dbms_output.put_line( ‘code = ‘ || vreply.code );
dbms_output.put_line( ‘text = ‘ || vreply.text );

dbms_output.put_line(‘help’);
dbms_output.put_line(‘—-‘);
vreplies := utl_smtp.help(mailCONN, ‘HELP’);
for i in 1..vreplies.count loop
dbms_output.put_line( ‘code = ‘ || vreplies(i).code );
dbms_output.put_line( ‘text = ‘ || vreplies(i).text );
end loop;

dbms_output.put_line(‘ehlo’);
dbms_output.put_line(‘—-‘);
vreplies := utl_smtp.ehlo(mailCONN, mailHOST);
for i in 1..vreplies.count loop
dbms_output.put_line( ‘code = ‘ || vreplies(i).code );
dbms_output.put_line( ‘text = ‘ || vreplies(i).text );
end loop;

–dbms_output.put_line(‘helo’);
–dbms_output.put_line(‘—-‘);
–vreply := utl_smtp.helo(mailCONN, mailHOST);
–dbms_output.put_line( ‘code = ‘ || vreply.code );
–dbms_output.put_line( ‘text = ‘ || vreply.text );

dbms_output.put_line(‘mail’);
dbms_output.put_line(‘—-‘);
vreply := utl_smtp.mail(mailCONN, mailFROM);
dbms_output.put_line( ‘code = ‘ || vreply.code );
dbms_output.put_line( ‘text = ‘ || vreply.text );

dbms_output.put_line(‘rcpt’);
dbms_output.put_line(‘—-‘);
vreply := utl_smtp.rcpt(mailCONN, mailTO);
dbms_output.put_line( ‘code = ‘ || vreply.code );
dbms_output.put_line( ‘text = ‘ || vreply.text );

dbms_output.put_line(‘open_data’);
dbms_output.put_line(‘———‘);
vreply := utl_smtp.open_data(mailCONN);
dbms_output.put_line( ‘code = ‘ || vreply.code );
dbms_output.put_line( ‘text = ‘ || vreply.text );

utl_smtp.write_data(mailCONN, ‘Subject: ‘|| ‘A subject’ || chr(13));
utl_smtp.write_data(mailCONN, ‘From: ‘||mailFROM || chr(13));
utl_smtp.write_data(mailCONN, ‘Date: ‘||mailDATE || chr(13));
utl_smtp.write_data(mailCONN, ‘To: ‘||mailTO || chr(13));
utl_smtp.write_data(mailCONN, ‘CC: ‘||mailFROM || chr(13));
utl_smtp.write_data(mailCONN, ‘BCC: ‘||mailFROM || chr(13));
utl_smtp.write_data(mailCONN, chr(13));
utl_smtp.write_data(mailCONN, ‘Hello Friend.’ || chr(13));
utl_smtp.write_data(mailCONN, ‘Drop me an e-mail.’ || chr(13));

dbms_output.put_line(‘close_data’);
dbms_output.put_line(‘———-‘);
vreply := utl_smtp.close_data(mailCONN);
dbms_output.put_line( ‘code = ‘ || vreply.code );
dbms_output.put_line( ‘text = ‘ || vreply.text );

dbms_output.put_line(‘quit’);
dbms_output.put_line(‘—-‘);
vreply := utl_smtp.quit(mailCONN);
dbms_output.put_line( ‘code = ‘ || vreply.code );
dbms_output.put_line( ‘text = ‘ || vreply.text );
END;
/

Listing 2

Output from sending a mail message


SQL > exec simpletextmessage
open_connection
—————
code = 220
text = mpls-pop-12.inet.qwest.net ESMTP

help
—-
code = 214
text = Contact [email protected] for assistance.

help – RCPT
—-
code = 214
text = Contact [email protected] for assistance.

ehlo
—-
code = 250
text = mpls-pop-12.inet.qwest.net
code = 250
text = PIPELINING
code = 250
text = 8BITMIME

mail
—-
code = 250
text = ok

rcpt
—-
code = 250
text = ok

open_data
———
code = 354
text = Please start mail input.

close_data
———-
code = 250
text = Mail queued for delivery.

quit
—-
code = 221
text = Closing connection. Good bye.

PL/SQL procedure successfully completed.

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.

»


See All Articles by Columnist
James Koopmann

James Koopmann
James Koopmann
James Koopmann has fourteen years of database design, development and performance tuning experience. In addition, he has extensive database administration experience in Oracle and other relational databases in production environments, specializing in performance tuning of database engines and SQL based applications. Koopmann is an accomplished author with several technical papers in various Oracle related publications such as Oracle Magazine, Oracle Professional and SQL>UPDATE_RMOUG. He is a featured author and database expert for DatabaseJournal, a member of the editorial review committee for Select Journal (The Magazine for the International Oracle Users Group), an Oracle Certified Professional DBA and noted speaker at local Oracle User Groups around the country.

Latest Articles