|
1、最简单的，就是一次发送给一个人，并且不支持 HTML 格式的
当然，前提必须一台SMTP服务器，而且只能发送内部邮件。
也就是和发送邮件地址在一个域内的
CREATE OR REPLACE
PROCEDURE sp_send_mail
( SENDER IN VARCHAR2,
RECIPIENT IN VARCHAR2,
SUBJECT IN VARCHAR2,
MESSAGE IN VARCHAR2)
IS
MAILHOST VARCHAR2(30) := '192.168.0.1';
MAIL_CONN UTL_SMTP.CONNECTION;
CRLF VARCHAR2( 2 ):= CHR( 13 ) || CHR( 10 );
MESG VARCHAR2( 1000 );
BEGIN
MAIL_CONN := UTL_SMTP.open_CONNECTION(MAILHOST, 25);
MESG:= 'Date: ' || TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' ) || CRLF ||
'From: <'||SENDER||'>' || CRLF ||
'SUBJECT: '||SUBJECT || CRLF ||
'To: '||RECIPIENT || CRLF ||
'' || CRLF || MESSAGE;
UTL_SMTP.HELO(MAIL_CONN, MAILHOST);
UTL_SMTP.MAIL(MAIL_CONN, SENDER);
UTL_SMTP.RCPT(MAIL_CONN, RECIPIENT);
UTL_SMTP.DATA(MAIL_CONN, MESG);
UTL_SMTP.QUIT(MAIL_CONN);
END;
2，支持发送HTML,支持都用户发送的邮件Package，但是需要提供SMTP服务器，用户名和密码的。
-- Start of DDL Script for Package APPS.XXMAIL_PKG
-- Generated 2006-9-26 16:20:38 from APPS@SUZ
CREATE OR REPLACE
PACKAGE xxmail_pkg
IS
-- ************************************************************************.
/*
Package Parameters, Please update them by yourself
*/
--SMTP Mail Server
smtp_host VARCHAR2(256) := '192.168.0.11';
--SMTP Mail Server Port
smtp_port PLS_INTEGER := 25;
--Regular Mail Subject
mail_subject VARCHAR2(300) :='Package Mail(SMTP) Testing';
--Mail Send Address
mail_sender VARCHAR2(200) :='xxx@xxx.com';
--mail_sender VARCHAR2(200) :='TOPEN_TAO@HZMOT.COM';
--Mail Server Login name
mail_username VARCHAR2(100) :='domain\username';
--Mail Server Login Password
mail_password VARCHAR2(100) :='password';
--Mail Recipients, separated by semicolon
mail_recipients VARCHAR2(200) :='xxx@xxx.com;xxx@itpub.net';
MAILER_ID CONSTANT VARCHAR2(256) := 'Mailer by Oracle UTL_SMTP';
-- ************************************************************************
-- Extended email API to send email in HTML or plain text with no size limit.
-- First, begin the email by begin_mail(). Then, call write_text() repeatedly
-- to send email in ASCII piece-by-piece. Or, call write_mb_text() to send
-- email in non-ASCII or multi-byte character set. End the email with
-- end_mail().
FUNCTION begin_mail
( sender IN VARCHAR2,
username IN VARCHAR2,
password IN VARCHAR2,
recipients IN VARCHAR2,
subject IN VARCHAR2,
mime_type IN VARCHAR2 DEFAULT 'text/plain',
priority IN PLS_INTEGER DEFAULT NULL
)
RETURN utl_smtp.connection;
FUNCTION begin_session(username IN VARCHAR2, password IN VARCHAR2) RETURN utl_smtp.connection;
-- Begin an email in a session.
PROCEDURE begin_mail_in_session(conn IN OUT NOCOPY utl_smtp.connection,
sender IN VARCHAR2,
recipients IN VARCHAR2,
subject IN VARCHAR2,
mime_type IN VARCHAR2 DEFAULT 'text/plain',
priority IN PLS_INTEGER DEFAULT NULL);
FUNCTION get_address(addr_list IN OUT VARCHAR2) RETURN VARCHAR2;
PROCEDURE write_mime_header(conn IN OUT NOCOPY utl_smtp.connection,
name IN VARCHAR2,
value IN VARCHAR2);
-- Write email body in ASCII
PROCEDURE write_text(conn IN OUT NOCOPY utl_smtp.connection,
message IN VARCHAR2);
-- End the email.
PROCEDURE end_mail(conn IN OUT NOCOPY utl_smtp.connection);
-- Write email body in non-ASCII (including multi-byte). The email body
-- will be sent in the database character set.
PROCEDURE write_mb_text(conn IN OUT NOCOPY utl_smtp.connection,
message IN VARCHAR2);
PROCEDURE SEND_EMAILS
(
p_recipients VARCHAR2 DEFAULT mail_recipients,
p_subject VARCHAR2 DEFAULT mail_subject,
p_mesg VARCHAR2
);
END; -- Package spec
/
CREATE OR REPLACE
PACKAGE BODY xxmail_pkg
IS
FUNCTION begin_mail(sender IN VARCHAR2,
username IN VARCHAR2,
password IN VARCHAR2,
recipients IN VARCHAR2,
subject IN VARCHAR2,
mime_type IN VARCHAR2 DEFAULT 'text/plain',
priority IN PLS_INTEGER DEFAULT NULL)
RETURN utl_smtp.connection
IS
conn utl_smtp.connection;
BEGIN
conn := begin_session(username, password);
begin_mail_in_session(conn, sender, recipients, subject, mime_type,priority);
RETURN conn;
END begin_mail;
FUNCTION begin_session(username IN varchar2, password IN varchar2) RETURN utl_smtp.connection
IS
conn utl_smtp.connection;
BEGIN
-- open SMTP connection
conn := utl_smtp.open_connection(smtp_host, smtp_port);
utl_smtp.ehlo(conn,smtp_host);
UTL_SMTP.command(conn,'AUTH LOGIN');
UTL_SMTP.command(conn, UTL_RAW.CAST_TO_VARCHAR2(Utl_Encode.base64_encode(utl_raw.cast_to_raw(username))));
UTL_SMTP.command(conn, UTL_RAW.CAST_TO_VARCHAR2(Utl_Encode.base64_encode(utl_raw.cast_to_raw(password))));
RETURN conn;
END begin_session;
PROCEDURE begin_mail_in_session(conn IN OUT NOCOPY utl_smtp.connection,
sender IN VARCHAR2,
recipients IN VARCHAR2,
subject IN VARCHAR2,
mime_type IN VARCHAR2 DEFAULT 'text/plain',
priority IN PLS_INTEGER DEFAULT NULL)
IS
my_recipients VARCHAR2(32767) := recipients;
my_sender VARCHAR2(32767) := sender;
BEGIN
-- Specify sender's address (our server allows bogus address
-- as long as it is a full email address (xxx@yyy.com).
--utl_smtp.helo(conn,smtp_host);
utl_smtp.mail(conn, get_address(my_sender));
-- Specify recipient(s) of the email.
WHILE (my_recipients IS NOT NULL) LOOP
utl_smtp.rcpt(conn, get_address(my_recipients));
END LOOP;
-- Start body of email
utl_smtp.open_data(conn);
-- Set "Date" MIME header
--write_mime_header(conn, 'Date', TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' ));
--write_mime_header(conn, 'Date', TO_CHAR( SYSDATE, 'DD-Mon-YYYY hh24:mi:ss' ));
-- Set "From" MIME header
write_mime_header(conn, 'From', sender);
-- Set "To" MIME header
write_mime_header(conn, 'To', recipients);
-- Set "Subject" MIME header
write_mime_header(conn, 'Subject', subject);
-- Set "Content-Type" MIME header
write_mime_header(conn, 'Content-Type', mime_type);
-- Set "X-Mailer" MIME header
write_mime_header(conn, 'X-Mailer', MAILER_ID);
-- Set priority:
-- High Normal Low
-- 1 2 3 4 5
IF (priority IS NOT NULL) THEN
write_mime_header(conn, 'X-Priority', priority);
END IF;
-- Send an empty line to denotes end of MIME headers and
-- beginning of message body.
utl_smtp.write_data(conn, utl_tcp.CRLF);
IF (mime_type LIKE 'multipart/mixed%') THEN
write_text(conn, 'This is a multi-part message in MIME format.' || utl_tcp.crlf);
END IF;
END begin_mail_in_session;
FUNCTION get_address(addr_list IN OUT VARCHAR2) RETURN VARCHAR2 IS
addr VARCHAR2(256);
i pls_integer;
FUNCTION lookup_unquoted_char
(
str IN VARCHAR2,
chrs IN VARCHAR2
)
RETURN pls_integer AS
c VARCHAR2(5);
i pls_integer;
len pls_integer;
inside_quote BOOLEAN;
BEGIN
inside_quote := false;
i := 1;
len := length(str);
WHILE (i <= len) LOOP
c := substr(str, i, 1);
IF (inside_quote) THEN
IF (c = '"') THEN
inside_quote := false;
ELSIF (c = '\') THEN
i := i + 1; -- Skip the quote character
END IF;
GOTO next_char;
END IF;
IF (c = '"') THEN
inside_quote := true;
GOTO next_char;
END IF;
IF (instr(chrs, c) >= 1) THEN
RETURN i;
END IF;
<<next_char>>
i := i + 1;
END LOOP;
RETURN 0;
END;
BEGIN
addr_list := ltrim(addr_list);
i := lookup_unquoted_char(addr_list, ',;');
IF (i >= 1) THEN
addr := substr(addr_list, 1, i - 1);
addr_list := substr(addr_list, i + 1);
ELSE
addr := addr_list;
addr_list := '';
END IF;
i := lookup_unquoted_char(addr, '<');
IF (i >= 1) THEN
addr := substr(addr, i + 1);
i := instr(addr, '>');
IF (i >= 1) THEN
addr := substr(addr, 1, i - 1);
END IF;
END IF;
RETURN addr;
END get_address;
PROCEDURE write_mime_header
(
conn IN OUT NOCOPY utl_smtp.connection,
name IN VARCHAR2,
value IN VARCHAR2
)
IS
BEGIN
utl_smtp.write_data(conn, name || ': ' || value || utl_tcp.CRLF);
END write_mime_header;
PROCEDURE write_text
(
conn IN OUT NOCOPY utl_smtp.connection,
message IN VARCHAR2
)
IS
BEGIN
utl_smtp.write_data(conn, message);
END write_text;
PROCEDURE end_mail(conn IN OUT NOCOPY utl_smtp.connection) IS
BEGIN
utl_smtp.close_data(conn);
utl_smtp.quit(conn);
END end_mail;
PROCEDURE write_mb_text(conn IN OUT NOCOPY utl_smtp.connection,
message IN VARCHAR2) IS
BEGIN
utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw(message));
END write_mb_text;
PROCEDURE SEND_EMAILS
(
p_recipients VARCHAR2 DEFAULT mail_recipients,
p_subject VARCHAR2 DEFAULT mail_subject,
p_mesg VARCHAR2
)
IS
conn UTL_SMTP.connection;
BEGIN
conn := begin_mail
(
sender => mail_sender,
username => mail_username,
PASSWORD => mail_password,
recipients => p_recipients,
subject => p_subject,
mime_type => 'text/html;charset=utf-8'
);
write_mb_text
(
conn => conn,
MESSAGE => p_mesg
);
end_mail (conn => conn);
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'+----------------------------------------------------------------------+');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'+ Error Happens in SEND_EMAILS +');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,sqlerrm);
dbms_output.put_line('sqlerrm='||sqlerrm);
END;
END;
/
-- End of DDL Script for Package APPS.XXMAIL_PKG
|
