Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: smtp via PL/SQL

Re: smtp via PL/SQL

From: Eca Eca <ecaeser_at_hotmail.com>
Date: Fri, 18 Jan 2002 03:40:22 -0800
Message-ID: <F001.003F391C.20020118030026@fatcity.com> 







Friend :



Take a look at this text below.


I think it helps you.


Begin of the text ---------------------------------------------






Bookmark Fixed font  Go to End



Doc ID:  Note:106513.1


Type:  SCRIPT


Status:  PUBLISHED


Content Type:  TEXT/PLAIN


Creation Date:  25-APR-2000


Last Revision Date:  19-JUL-2000


Language:  USAENG




Overview





The UTL_SMTP package is a new package included in the Oracle8i Release 2


(8.1.6).  This package allows PL/SQL to generate e-mail messages using the

UTL_TCP package.  UTL_SMTP is discussed in Chapter 65 of the "Oracle8i
Supplied PL/SQL Packages Reference Release 2 (8.1.6)".



To use this package, the Java option must be installed in the database and 
the


TCPConnection class (from $ORACLE_HOME/plsql/jlib/plsql.jar) must be loaded.




Generating E-mail Using UTL_SMTP





Perform the following steps to generate e-mail messages using the UTL_SMTP
package:



    
  
  1.   Establish a connection with the SMTP server (usually port 25).  This is
     done through a UTL_SMTP.OPEN_CONNECTION() function call.  This function
     returns the connection record to be used in subsequent calls.

  
  2.   Perform the initial handshake with the SMTP server.  This is done 
through
     a UTL_SMTP.HELO() call, or optionally through the UTL_SMTP.EHLO() call.

  
  3.   Start the mail message by specifying the 'From' mail ID.  This is done
     through a UTL_SMTP.MAIL() call.

  
  4.   Specify the recipients for the e-mail message.  This can be done 
through
     the UTL_SMTP.RCPT() call.

  
  5.   Assemble the body of the e-mail message with the redundant recipient 
list.
     If the body of the e-mail does not contain this information, it will 
not
     be received in the resulting e-mail.






     When assembling the body, RFC 821 requires that the lines to be 
terminated

     by <CR><LF> which is a character 13 and character 10 (accomplished in
     PL/SQL by using: CHR(13)||CHAR(10)).

  6) Pass the body of the message into the UTL_SMTP buffer by calling the
     UTL_SMTP.DATA().

     Note:  This function performs the RFC specified termination of
            <CR><LF>.<CR><LF> to denote the end of the data.





  7) Close the SMTP connection through the UTL_SMTP.QUIT() call.



The UTL_SMTP has several variations of all of the above routines.  The 
samples


provided below only use the procedural method, but can be modified to use 
the


function methods to obtain more information on return messages from the SMTP
commands.



Returns are returned to PL/SQL into a record type called UTL_SMTP.REPLY.  A
second procedure method allows PL/SQL INDEX-BY tables as a parameter to get
more messages.




Program Notes





  o  The code below relies on a table called USER_MAILIDS.  Modify the 
script


     below to generate using valid e-mail addresses, groups, and IDs.



  o  Ensure that the UTL_SMTP package is setup by running the Java VM script


     $ORACLE_HOME/javavm/install/initjvm.sql, loading the plsql.jar 


(loadjava)


     from $ORACLE_HOME/plsql/jlib, and running $ORACLE_HOME/rdbms/admin/
     initplsj.sql






References




RFC 821 (www.ietf.org/rfc/rfc0821.txt)
RFC 822 (www.ietf.org/rfc/rfc0822.txt)
"Oracle8i Supplied PL/SQL Packages Reference Release 2 (8.1.6)", (A76936-01)






Caution





The sample program in this article is provided for educational purposes only
and is NOT supported by Oracle Support Services.  It has been tested
internally, however, and works as documented.  We do not guarantee that it
will work for you, so be sure to test it in your environment before relying
on it.




SQL Script





buildtab.sql


- - - - - - - - - - - - - - - - Code begins here - - - - - - - - - - - - - - 
- -



CREATE TABLE user_mailids (

  user_alias   VARCHAR2( 30 ),
  user_fname   VARCHAR2( 100 ),
  user_email   VARCHAR2( 100 ),
  user_group   VARCHAR2( 30 )




);


INSERT INTO user_mailids VALUES( 'user1', 'User 1',
   'eca_at_widesoft.com.br', 'group1' );
INSERT INTO user_mailids VALUES( 'user2', 'User 2',




   'layres_at_widesoft.com.br', 'group2' );

INSERT INTO user_mailids VALUES( 'user3', 'User 3',
   'user3_at_us.oracle.com', 'group1' );
INSERT INTO user_mailids VALUES( 'scott', 'Scott DBA',




   'user4_at_us.oracle.com', 'groupdba' );



COMMIT;







Procedures





mailit.pls


- - - - - - - - - - - - - - - - Code begins here - - - - - - - - - - - - - - 
- -



CREATE OR REPLACE PACKAGE mailit AS


  TYPE addresslist_tab IS TABLE OF VARCHAR2( 200 )
    INDEX BY BINARY_INTEGER;


  PROCEDURE mailusers( to_list IN VARCHAR2, cc_list IN VARCHAR2,
    subj IN VARCHAR2, body IN VARCHAR2 );
  PROCEDURE mailgroups( to_list IN VARCHAR2, cc_list IN VARCHAR2,
    subj IN VARCHAR2, body IN VARCHAR2 );
END;



/



CREATE OR REPLACE PACKAGE BODY mailit AS
  PROCEDURE mailusers( to_list IN VARCHAR2, cc_list IN VARCHAR2,
    subj IN VARCHAR2, body IN VARCHAR2 ) IS
    conn UTL_SMTP.CONNECTION;


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


    usrname VARCHAR2( 30 );


    usraddr VARCHAR2( 100 );


    CURSOR get_user IS SELECT user_fname, user_email
      FROM user_mailids WHERE user_alias = lower( user );
    CURSOR get_list ( v_tempstr IN VARCHAR2 ) IS

      SELECT user_fname, user_email
      FROM user_mailids
      WHERE v_tempstr LIKE '%' || user_alias || '%';


    addrlist addresslist_tab;


    addrcnt  BINARY_INTEGER:= 0;


  BEGIN



    OPEN get_user;


    FETCH get_user INTO usrname, usraddr;
    IF get_user%NOTFOUND THEN

      CLOSE get_user;
      RAISE_APPLICATION_ERROR( -20015, 'User not entered in USER_MAILIDS' );


    END IF;



    CLOSE get_user;


    conn:= utl_smtp.open_connection( 'smtp-gw.widesoft.com.br', 25 );
    utl_smtp.helo( conn, 'smtp-gw.widesoft.com.br' );
    utl_smtp.mail( conn, usraddr );


    FOR listrec IN get_list( to_list ) LOOP

      utl_smtp.rcpt( conn, listrec.user_email );
      addrcnt:= addrcnt + 1;
      addrlist( addrcnt ):= 'To: ' || listrec.user_fname ||
         '<' || listrec.user_email || '>' || crlf;


    END LOOP;



    IF addrcnt = 0 THEN


      RAISE_APPLICATION_ERROR( -20016, 'No To: list generated' );
    END IF;



    FOR listrec IN get_list( cc_list ) LOOP

      utl_smtp.rcpt( conn, 'cc:' || listrec.user_email );
      addrcnt:= addrcnt + 1;
      addrlist( addrcnt ):= 'Cc: ' || listrec.user_fname ||
         '<' || listrec.user_email || '>' || crlf;


    END LOOP;



    mesg:= 'Date: ' || TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' ) || crlf ||
           'From: ' || usrname || ' <' || usraddr || '>' || crlf ||
           'Subject: ' || subj || crlf;
    FOR i IN 1 .. addrcnt LOOP
      mesg:= mesg || addrlist( i );




    END LOOP;



    mesg:= mesg || '' || crlf || body;



    utl_smtp.data( conn, mesg );


    utl_smtp.quit( conn );


  END;


  PROCEDURE mailgroups( to_list IN VARCHAR2, cc_list IN VARCHAR2,
    subj IN VARCHAR2, body IN VARCHAR2 ) IS
    conn UTL_SMTP.CONNECTION;


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


    usrname VARCHAR2( 30 );


    usraddr VARCHAR2( 100 );


    CURSOR get_user IS SELECT user_fname, user_email
      FROM user_mailids WHERE user_alias = lower( user );
    CURSOR get_list ( v_tempstr IN VARCHAR2 ) IS

      SELECT user_fname, user_email
      FROM user_mailids
      WHERE v_tempstr LIKE '%' || user_group || '%';


    addrlist addresslist_tab;


    addrcnt  BINARY_INTEGER:= 0;


  BEGIN



    OPEN get_user;


    FETCH get_user INTO usrname, usraddr;
    IF get_user%NOTFOUND THEN

      CLOSE get_user;
      RAISE_APPLICATION_ERROR( -20015, 'User not entered in USER_MAILIDS' );


    END IF;



    CLOSE get_user;



    conn:= utl_smtp.open_connection( 'smtp-gw.widesoft.com.br', 25 );
    utl_smtp.helo( conn, 'smtp-gw.widesoft.com.br' );
    utl_smtp.mail( conn, usraddr );


    FOR listrec IN get_list( to_list ) LOOP

      utl_smtp.rcpt( conn, listrec.user_email );
      addrcnt:= addrcnt + 1;
      addrlist( addrcnt ):= 'To: ' || listrec.user_fname ||
         '<' || listrec.user_email || '>' || crlf;


    END LOOP;



    IF addrcnt = 0 THEN


      RAISE_APPLICATION_ERROR( -20016, 'No To: list generated' );
    END IF;



    FOR listrec IN get_list( cc_list ) LOOP

      utl_smtp.rcpt( conn, 'cc:' || listrec.user_email );
      addrcnt:= addrcnt + 1;
      addrlist( addrcnt ):= 'Cc: ' || listrec.user_fname ||
         '<' || listrec.user_email || '>' || crlf;


    END LOOP;



    mesg:= 'Date: ' || TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' ) || crlf ||
           'From: ' || usrname || ' <' || usraddr || '>' || crlf ||
           'Subject: ' || subj || crlf;
    FOR i IN 1 .. addrcnt LOOP
      mesg:= mesg || addrlist( i );




    END LOOP;



    mesg:= mesg || '' || crlf || body;



    utl_smtp.data( conn, mesg );


    utl_smtp.quit( conn );


  END;



END;



/






Example Usage





testit.pls


- - - - - - - - - - - - - - - - Code begins here - - - - - - - - - - - - - - 
- -


INSERT INTO user_mailids VALUES( 'eca', 'eca', 'eca_at_widesoft.com.br', 
'group1' );


INSERT INTO user_mailids VALUES( 'Alexandre', 'Alexandre', 
'zia_at_widesoft.com.br', 'group1' );

INSERT INTO user_mailids VALUES( 'Luiz Ayres', 'Luiz Ayres', 
'layres_at_widesoft.com.br', 'group1' );
INSERT INTO user_mailids VALUES( 'Belkis', 'Belkis', 
'belkis_at_widesoft.com.br', 'group1' );
INSERT INTO user_mailids VALUES( 'Fabiano', 'Fabiano', 


'meds_at_widesoft.com.br', 'group1' );


INSERT INTO user_mailids VALUES( 'Missao', 'Missao', 
'missao_at_widesoft.com.br', 'group1' );



select

substr(USER_ALIAS,1,15),
substr(USER_FNAME,1,15),
substr(USER_EMAIL,1,30),
substr(USER_GROUP,1,15)




from user_mailids


/




BEGIN



  mailit.mailusers('user1','user2','ALERT: Test being conducted',
    'Notice to users: This is a test!!!!' );
  mailit.mailgroups('group1,group2', NULL,'WARNING: Test being conducted',
    'Notice to groups: This is a test!!!!' );
END;



/



BEGIN


  mailit.mailusers('eca','eca',
                   'ALERT: Teste de e-mail enviado por package do Oracle',
                   'Pessoal :
                    Este é um teste de envio de e-mail através de uma 
package do Oracle.
                    Conforme documentação encontrada no metalink esta 
package é nova e roda
                    na versão 8.1.6 do Oracle.

                    Sem mais

                    Eriovaldo' );




END;



/



BEGIN



  mailit.mailgroups('group1', NULL,

                   'ALERT: Teste de e-mail enviado por package do Oracle',
                   'Pessoal :
                    Este é um teste de envio de e-mail através de uma 
package do Oracle.
                    Conforme documentação encontrada no metalink esta 
package é nova e roda
                    na versão 8.1.6 do Oracle.

                    Sem mais

                    Eriovaldo' );




END;



/



BEGIN



  mailit.mailgroups('group1', NULL,'WARNING: Test being conducted',
    'Notice to groups: This is a test!!!!' );
END;



/







Sample Output





Email 1:



   Date: 25 Apr 00 14:48:00
   From: Scott DBA <user4_at_us.oracle.com>
     To: User 1<user1_at_us.oracle.com>
     CC: User 2<user2_at_us.oracle.com>







Notice to users: This is a test!!!!



Email 2:



   Date: 25 Apr 00 14:48:05
   From: Scott DBA <user4_at_us.oracle.com>
     To: User 1<user1_at_us.oracle.com>, User 2<user2_at_us.oracle.com>,
         User 3<user3_at_us.oracle.com>







Notice to groups: This is a test!!!!




Additional Search Words





using utl_smtp


.








Copyright (c) 1995,2000 Oracle Corporation. All Rights Reserved. Legal 
Notices and Terms of Use.



end od the text -----------------------------------------------

>From: "Steve McClure" <smcclure_at_usscript.com>
>Reply-To: ORACLE-L_at_fatcity.com
>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>Subject: smtp via PL/SQL
>Date: Thu, 17 Jan 2002 16:35:36 -0800
>
>I am digging into the docs I can find on utl_smtp and utl_tcp, but I am
>really not finding much.  I have Oracle's package reference docs, but that
>doesn't shed all that much light on the subject.  I am pretty well a newbie
>to tcp and smtp.
>
>Geeze all that talking and no question yet.  Can anyone recommend a book or
>white paper on implementing 'email' from within an Oracle database?  I have
>downloaded some sample code from Orafaq, and actually gotten it working on
>our db.  I would just like to actually understand what I am doing, and
>expand on what we have.
>
>Steve McClure
>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author: Steve McClure
>   INET: smcclure_at_usscript.com
>
>Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
>San Diego, California        -- Public Internet access / Mailing Lists
>--------------------------------------------------------------------
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L


>(or the name of mailing list you want to be removed from).  You may
>also send the HELP command for other information (like subscribing).








O MSN Photos é o jeito mais fácil de compartilhar e imprimir as suas fotos: 
http://photos.msn.com.br/support/worldwide.aspx


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Eca Eca
  INET: ecaeser_at_hotmail.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).


Received on Fri Jan 18 2002 - 05:40:22 CST












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US