Doc ID:106513.1 Type: BULLETIN
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_SMTP package. UTL_SMTP is discussed in the documentation
'PL/SQL Packages and Types Reference'.
When using Oracle 8i (8.1.5 to 8.1.7) it is required to install the Oracle JVM
inside the database, because the UTL_SMTP is implemented by usage of mapped
JVM libraries - i.e. JAVA classes. See Note 132936.1
Beginning with Oracle 9i (9.0.1) and higher, the UTL_SMTP is using library
functions implemented in C for gaining performance and enabling databases
to use these packages without installed JVM.
To install the UTL_SMTP package, use SQL*Plus and run as user "SYS"
the following files located in ORACLE_HOME/rdbms/admin
utlsmtp.sql
prvtsmtp.plb
additionally in 10g it is possible to use UTL_MAIL
utlmail.sql
prvtmail.plb
See also Note 369777.1
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.
References
----------
RFC 821 (www.ietf.org/rfc/rfc0821.txt)
RFC 822 (www.ietf.org/rfc/rfc0822.txt)
Supplied PL/SQL Packages and Types Reference Release 2 (9.2)
PL/SQL Packages and Types Reference 10g Release 2 (10.2)
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',
'user1@us.oracle.com', 'group1' );
INSERT INTO user_mailids VALUES( 'user2', 'User 2',
'user2@us.oracle.com', 'group2' );
INSERT INTO user_mailids VALUES( 'user3', 'User 3',
'user3@us.oracle.com', 'group1' );
INSERT INTO user_mailids VALUES( 'scott', 'Scott DBA',
'user4@us.oracle.com', '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( to_list );
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( 'smtp01.us.oracle.com', 25 );
utl_smtp.helo( conn, 'smtp01.us.oracle.com' );
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_group = lower( to_list );
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( 'smtp01.us.oracle.com', 25 );
utl_smtp.helo( conn, 'smtp01.us.oracle.com' );
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( SYSTIMESTAMP, 'dd Mon yy hh24:mi:ss TZHTZM' ) || 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 - - - - - - - - - - - - - - - -
BEGIN
mailit.mailusers('user1','user2','ALERT: Test being conducted',
'Notice to users: This is a test!!!!' );
mailit.mailgroups('group1','group2','WARNING: Test being conducted',
'Notice to groups: This is a test!!!!' );
END;
/
- - - - - - - - - - - - - - - - Code ends here - - - - - - - - - - - - - - - -
Sample Output
-------------
Email 1:
--------
Subject: ALERT: Test being conducted
Date: 25 Apr 00 14:48:00
From: Scott DBA <user4@us.oracle.com>
To: User 1<user1@us.oracle.com>
CC: User 2<user2@us.oracle.com>
Notice to users: This is a test!!!!
Email 2:
--------
Subject: WARNING: Test being conducted
Date: 25 Apr 00 14:48:05
From: Scott DBA <user4@us.oracle.com>
To: User 1<user1@us.oracle.com>, User 2<user2@us.oracle.com>,
User 3<user3@us.oracle.com>
Notice to groups: This is a test!!!!
Additional Search Words
-----------------------
using utl_smtp
全站熱搜
留言列表
Oracle eBS (11.5.10.2) (13)