Solved

UTL_SMTP

Posted on 2003-11-07
4
4,338 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 23

Expert Comment

by:seazodiac
ID: 9701515
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 48

Expert Comment

by:schwertner
ID: 9702063
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
ID: 9702220
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
ID: 9716204
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Database Design Dilemma 6 66
setting local variables in a cursor block 3 30
Excess Redo 3 32
Oracle programming for starter 14 37
Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious sideā€¦
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

730 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