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 := ‘sendFROM@domain.com’;
    mailTO   := ‘sendTO@domain.com’;
    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 postmaster@uswest.net for assistance.
help – RCPT
—-
code = 214
text = Contact postmaster@uswest.net 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