Solved

UTL_SMTP

Posted on 2003-11-07
4
4,329 Views
Last Modified: 2007-12-19
I need a real life example on how to use UTL_SMTP package to send e-mail from oracle procedure
0
Comment
Question by:ipesin
4 Comments
 
LVL 23

Expert Comment

by:seazodiac
Comment Utility
Here is one:

I used this for my database before Java stored procedure comes along. I got this originally from the web post too.

Here is a  simple PLSQL to send email from Oracle Database (8.x) and higher in HTML format (so plain text is easier).  all you need is to change the sendorAddress, receiverAddress and smtp host.

----- Cut it from Here---------------------

Declare
 

  SendorAddress  Varchar2(30)  := 'swadhwa@TEST.com';  

   /* Address of the person who is sending Email */

 

  ReceiverAddress varchar2(30) := 'DBA@Test.com';

 /* Address of the person who is receiving Email */

 

  EmailServer     varchar2(30) := 'mail.Test.com';

 /* Address of your Email Server Configured for sending emails */

 

  Port number  := 25;

  /*  Port Number responsible for sending email */

 

  conn UTL_SMTP.CONNECTION;

  /* UTL_SMTP package establish a connection with the SMTP server */

 

  crlf VARCHAR2( 2 ):= CHR( 13 ) || CHR( 10 );

  /* crlf  used  for carriage return */

 

 mesg VARCHAR2( 4000 );

 /*  Variable for storing message contents */

 

mesg_body varchar2(4000)

/* Variable for storing HTML code */

   := '    <html>

            <head>

            <title>Oracle Techniques By Sameer Wadhwa</title>

            </head>

            <body bgcolor="#FFFFFF" link="#000080">

            <table cellspacing="0" cellpadding="0" width="100%">

            <tr align="LEFT" valign="BASELINE">

            <td width="100%" valign="middle"><h1><font color="#00008B"><b>Send Mail in HTML                      Format</b></font></h1>

             </td>

         </table>

          <ul>

           <li><b><a href="www.geocities.com/samoracle">Oracle Techniques is for DBAs </li>

           <l><b>                                     by Sameer Wadhwa </b> </l>              

              </ul>

             </body>

             </html>';

 

BEGIN

 

  /* Open Connection */

 

  conn:= utl_smtp.open_connection( EmailServer, Port );  

 

 /* Hand Shake */

 

  utl_smtp.helo( conn, EmailServer );

 

 /* Configure Sender and Recipient  with UTL_SMTP */

 

  utl_smtp.mail( conn, SendorAddress);

  utl_smtp.rcpt( conn, ReceiverAddress );

 

/* Making Message buffer */

 

  mesg:=

        'Date: '||TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' )|| crlf ||

         'From:'||SendorAddress|| crlf ||

         'Subject: Mail Through ORACLE Database' || crlf ||

         'To: '||ReceiverAddress || crlf ||

         '' || crlf ||mesg_body||'';

 

 

/* Configure Sending Message */

/*You need to put 'MIME-Verion: 1.0' (this is case-sensitive!) */

 /*Content-Type-Encoding is actually Content-Transfer-Encoding. */

/*The MIME-Version, Content-Type, Content-Transfer-Encoding should */

/* be the first 3 data items in your message */

 

utl_smtp.data(conn, 'MIME-Version: 1.0' ||CHR(13)|| CHR(10)||'Content-type: text/html' || CHR(13)||CHR(10)||mesg);    

 

/* Closing Connection */

 

utl_smtp.quit( conn );

 

/* End of logic */

 

END;

/

 
0
 
LVL 47

Expert Comment

by:schwertner
Comment Utility
PROCEDURE SEND_EMAIL (sender IN VARCHAR2,
recipient IN VARCHAR2,
message IN VARCHAR2)
IS
mailhost VARCHAR2(30) := 'smtp.aaaaa.com';
mail_conn utl_smtp.connection;
BEGIN
mail_conn := utl_smtp.open_connection(mailhost, 25);
utl_smtp.helo(mail_conn, mailhost);
utl_smtp.mail(mail_conn, sender);
utl_smtp.rcpt(mail_conn, recipient);
utl_smtp.data(mail_conn, message);
utl_smtp.quit(mail_conn);
-- Handle the error
END;


You need version >=8.1.6, and Java installed for this to work.
Create a job that checks what it needs to check and when needed, runs this:

PROCEDURE SEND_MAIL
(V_MAILTO IN VARCHAR2
,V_MAILFROM IN VARCHAR2
,V_SUBJECT IN VARCHAR2
,V_BODY IN LONG
,V_CC IN VARCHAR2 := null
,V_BCC IN VARCHAR2 := null
)
IS
-- PL/SQL Specification
CONN UTL_SMTP.CONNECTION;
dato varchar2(4000);

-- PL/SQL Block
BEGIN
DECLARE
CONN UTL_SMTP.CONNECTION;
dato VARCHAR2(4000);
nSqlCode NUMBER(8);
vSqlErrm VARCHAR2(4000);
vSrv VARCHAR2(100);

BEGIN

vSrv := 'smtp.server.com;
CONN := UTL_SMTP.OPEN_CONNECTION(vSrv);
UTL_SMTP.HELO(CONN,'Origin.server.com');
UTL_SMTP.MAIL(CONN,v_mailfrom);
UTL_SMTP.RCPT(CONN,v_mailto);
UTL_SMTP.OPEN_DATA(CONN);

UTL_SMTP.WRITE_DATA(CONN,'Date:'||TO_CHAR(SYSDATE,'dd mon rrrr hh24:mi:ss')||utl_tcp.CRLF);
UTL_SMTP.WRITE_DATA(CONN,'From:'||v_mailfrom||utl_tcp.CRLF);
UTL_SMTP.WRITE_DATA(CONN,'To:'||v_mailto||utl_tcp.CRLF);
UTL_SMTP.WRITE_DATA(CONN,'Subject:'||v_subject||utl_tcp.CRLF);
UTL_SMTP.WRITE_DATA(CONN,utl_tcp.CRLF);

-- Splits body (long) into varchar2s.

FOR I IN 0..CEIL(LENGTH(V_BODY)/2000)-1 LOOP

DATO:=SUBSTR(V_BODY,I*2000+1,2000);
UTL_SMTP.WRITE_DATA(CONN,DATO);

END LOOP;

UTL_SMTP.CLOSE_DATA(CONN);
UTL_SMTP.QUIT(CONN);


EXCEPTION
WHEN utl_smtp.transient_error THEN
nSqlCode := SQLCODE;
vSqlErrm := SQLERRM;

BEGIN
UTL_SMTP.CLOSE_DATA(CONN);
EXCEPTION WHEN OTHERS THEN NULL;
END;

BEGIN
utl_smtp.quit(conn);
EXCEPTION WHEN OTHERS THEN NULL;
END;

raise_application_error(-20100, vSqlErrm);

WHEN utl_smtp.permanent_error THEN
nSqlCode := SQLCODE;
vSqlErrm := SQLERRM;
BEGIN
UTL_SMTP.CLOSE_DATA(CONN);
EXCEPTION WHEN OTHERS THEN NULL;
END;

BEGIN
utl_smtp.quit(conn);
EXCEPTION WHEN OTHERS THEN NULL;
END;

raise_application_error(-20200, vSqlErrm);
END;
END SEND_MAIL;
0
 
LVL 8

Expert Comment

by:Danielzt
Comment Utility
Please read this.
This is good paper for how to set up and use UTL_SMTP.

http://www.geocities.com/samoracle/swHowtoSendMail.htm
0
 
LVL 9

Accepted Solution

by:
konektor earned 500 total points
Comment Utility
it's better to use utl_tcp instead of utl_smpt (it calls it)

http://www.orafaq.com/scripts/plsql/smtp-att.txt
change IP address in procedure ...


CREATE OR REPLACE PROCEDURE sam_send_mail (
  ivMsgFrom     IN  VARCHAR2,              -- MAIL BOX SENDING THE EMAIL
  ivMsgFromName IN  VARCHAR2,              -- sender name
  ivMsgTo       IN  VARCHAR2,              -- list of MAIL BOX RECIEVING THE EMAIL separated by semicolon
  ivMsgSubject  IN  VARCHAR2,              -- EMAIL SUBJECT
  ivMsgText     IN  VARCHAR2,              -- mail body
  ivAttach      IN  VARCHAR2 DEFAULT NULL,
  ovReport      OUT VARCHAR2)
IS
  vConn       utl_tcp.connection;
  vBWriten    PLS_INTEGER;
  vReport     VARCHAR2(32767) := NULL;
  cMailServer CONSTANT VARCHAR2(100) := '<enter IP address of mail server here>';
  --
  TYPE tTabRcpt IS TABLE OF VARCHAR2(1000) INDEX BY BINARY_INTEGER;
  vTabRcpt    tTabRcpt;
  i           NUMBER := 1;
  --
  PROCEDURE Report IS
  BEGIN
    vReport := vReport || utl_tcp.get_line(vConn, TRUE) || chr(10);
  END;
  --
  PROCEDURE FillRcpt IS
    vScPos     NUMBER;
    vScLastPos NUMBER := 1;
    vRcpt      VARCHAR2(1000);
  BEGIN
    LOOP
      vScPos := instr(ivMsgTo, ';', vScLastPos);
      IF vScPos = 0 THEN
        vRcpt := rtrim(ltrim(substr(ivMsgTo, vScLastPos), ' '), ' ');
        IF vRcpt IS NOT NULL THEN
          vTabRcpt(i) := vRcpt;
        END IF;
        EXIT;
      ELSE
        vRcpt := rtrim(ltrim(substr(ivMsgTo, vScLastPos, vScPos-1),' '), ' ');
        IF vRcpt IS NOT NULL THEN
          vTabRcpt(i) := vRcpt;
          i := i + 1;
        END IF;
        vScLastPos := vScPos + 1;
      END IF;
    END LOOP;
  END;
  --
BEGIN
  FillRcpt;
  vConn := utl_tcp.open_connection(cMailServer, 25);                -- OPEN SMTP PORT CONNECTION
  vBWriten := utl_tcp.write_line(vConn, 'HELO '||cMailServer);      -- PERFORMS HANDSHAKING WITH SMTP SERVER
  Report;
  vBWriten := utl_tcp.write_line(vConn, 'EHLO '||cMailServer);      -- PERFORMS HANDSHAKING WITH SMTP SERVER, INCLUDING EXTRA INFORMATION
  Report;
  vBWriten := utl_tcp.write_line(vConn, 'MAIL FROM: '||ivMsgFrom);  -- MAIL BOX SENDING THE EMAIL
  Report;
  i := vTabRcpt.first;
  WHILE i is not NULL LOOP
    vBWriten := utl_tcp.write_line(vConn, 'RCPT TO: '||vTabRcpt(i));      -- MAIL BOX RECIEVING THE EMAIL
    i := vTabRcpt.next(i);
  END LOOP;
  Report;
  vBWriten := utl_tcp.write_line(vConn, 'DATA');                    -- EMAIL MESSAGE BODY START
  Report;
  vBWriten := utl_tcp.write_line(vConn, 'Date: '||TO_CHAR( SYSDATE, 'DD Mon YY hh24:mi:ss' ));
  vBWriten := utl_tcp.write_line(vConn, 'From: "'||ivMsgFromName||'" <'||ivMsgFrom||'>');
  vBWriten := utl_tcp.write_line(vConn, 'MIME-Version: 1.0');
  i := vTabRcpt.first;
  WHILE i is not NULL LOOP
    vBWriten := utl_tcp.write_line(vConn, 'To: '||vTabRcpt(i)||' <'||vTabRcpt(i)||'>');
    i := vTabRcpt.next(i);
  END LOOP;
  vBWriten := utl_tcp.write_line(vConn, 'Subject: '||ivMsgSubject);
  vBWriten := utl_tcp.write_line(vConn, 'Content-Type: multipart/mixed;');     -- INDICATES THAT THE BODY CONSISTS OF MORE THAN ONE PART
  vBWriten := utl_tcp.write_line(vConn, ' boundary="-----SECBOUND"');          -- SEPERATOR USED TO SEPERATE THE BODY PARTS
  vBWriten := utl_tcp.write_line(vConn, '');                                   -- INSERTS A BLANK LINE. PART OF THE MIME FORMAT AND NONE OF THEM SHOULD BE REMOVED.
  vBWriten := utl_tcp.write_line(vConn, '-------SECBOUND');
  vBWriten := utl_tcp.write_line(vConn, 'Content-Type: text/plain');           -- 1ST BODY PART. EMAIL TEXT MESSAGE
  vBWriten := utl_tcp.write_line(vConn, 'Content-Transfer-Encoding: 7bit');
  vBWriten := utl_tcp.write_line(vConn, '');
  vBWriten := utl_tcp.write_line(vConn, ivMsgText);                            -- TEXT OF EMAIL MESSAGE
  vBWriten := utl_tcp.write_line(vConn, '');
  vBWriten := utl_tcp.write_line(vConn, '-------SECBOUND');
  vBWriten := utl_tcp.write_line(vConn, 'Content-Type: text/plain;');          -- 2ND BODY PART.
  vBWriten := utl_tcp.write_line(vConn, ' name="Test.txt"');
  vBWriten := utl_tcp.write_line(vConn, 'Content-Transfer_Encoding: 8bit');
  vBWriten := utl_tcp.write_line(vConn, 'Content-Disposition: attachment;');   -- INDICATES THAT THIS IS AN ATTACHMENT
  vBWriten := utl_tcp.write_line(vConn, ' filename="Test.txt"');               -- SUGGESTED FILE NAME FOR ATTACHMENT
  vBWriten := utl_tcp.write_line(vConn, '');
  vBWriten := utl_tcp.write_line(vConn, ivAttach);
  vBWriten := utl_tcp.write_line(vConn, '-------SECBOUND--');
  vBWriten := utl_tcp.write_line(vConn, '');
  vBWriten := utl_tcp.write_line(vConn, '.');                       -- EMAIL MESSAGE BODY END
  Report;
  vBWriten := utl_tcp.write_line(vConn, 'QUIT');                    -- ENDS EMAIL TRANSACTION
  Report;
  utl_tcp.close_connection(vConn);                                  -- CLOSE SMTP PORT CONNECTION
  ovReport := vReport;
END;
/
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now