Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted Oct 21, 2004

Sending e-mail from within Oracle

By James Koopmann

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



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM


















Thanks for your registration, follow us on our social networks to keep up-to-date