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',
'[EMAIL PROTECTED]', 'group1' );
INSERT INTO user_mailids VALUES( 'user2', 'User 2',
'[EMAIL PROTECTED]', 'group2' );
INSERT INTO user_mailids VALUES( 'user3', 'User 3',
'[EMAIL PROTECTED]', 'group1' );
INSERT INTO user_mailids VALUES( 'scott', 'Scott DBA',
'[EMAIL PROTECTED]', 'groupdba' );
COMMIT;
- - - - - - - - - - - - - - - - Code ends here - - - - - - - - - - - - - -
- -
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;
/
- - - - - - - - - - - - - - - - Code ends here - - - - - - - - - - - - - -
- -
Example Usage
-------------
testit.pls
- - - - - - - - - - - - - - - - Code begins here - - - - - - - - - - - - - -
- -
INSERT INTO user_mailids VALUES( 'eca', 'eca', '[EMAIL PROTECTED]',
'group1' );
INSERT INTO user_mailids VALUES( 'Alexandre', 'Alexandre',
'[EMAIL PROTECTED]', 'group1' );
INSERT INTO user_mailids VALUES( 'Luiz Ayres', 'Luiz Ayres',
'[EMAIL PROTECTED]', 'group1' );
INSERT INTO user_mailids VALUES( 'Belkis', 'Belkis',
'[EMAIL PROTECTED]', 'group1' );
INSERT INTO user_mailids VALUES( 'Fabiano', 'Fabiano',
'[EMAIL PROTECTED]', 'group1' );
INSERT INTO user_mailids VALUES( 'Missao', 'Missao',
'[EMAIL PROTECTED]', '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;
/
- - - - - - - - - - - - - - - - Code ends here - - - - - - - - - - - - - -
- -
Sample Output
-------------
Email 1:
--------
Date: 25 Apr 00 14:48:00
From: Scott DBA <[EMAIL PROTECTED]>
To: User 1<[EMAIL PROTECTED]>
CC: User 2<[EMAIL PROTECTED]>
Notice to users: This is a test!!!!
Email 2:
--------
Date: 25 Apr 00 14:48:05
From: Scott DBA <[EMAIL PROTECTED]>
To: User 1<[EMAIL PROTECTED]>, User 2<[EMAIL PROTECTED]>,
User 3<[EMAIL PROTECTED]>
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" <[EMAIL PROTECTED]>
>Reply-To: [EMAIL PROTECTED]
>To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
>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: [EMAIL PROTECTED]
>
>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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
