查看: 32717|回复: 57

[精华] Package里面发送邮件简单介绍

[复制链接]
论坛徽章:
8
发表于 2006-9-17 20:58 | 显示全部楼层 |阅读模式
前段时间用到了在Package里面利用 SMTP 发送邮件的功能,大致的给大家分享一下,有三种情况

1、最简单的,就是一次发送给一个人,并且不支持 HTML 格式的
2、发送多人,并且支持 HTML 邮件格式
3、发送多人,并且支持 HTML 邮件格式,支持附件

后面我会边整理,边一一的给出代码,相信大家在网络上面也都能找到不错的参考资料
论坛徽章:
8
 楼主| 发表于 2006-9-17 20:59 | 显示全部楼层
[B]1、最简单的,就是一次发送给一个人,并且不支持 HTML 格式的
当然,前提必须一台SMTP服务器,而且只能发送内部邮件。
也就是和发送邮件地址在一个域内的

[/COLOR] [/B]



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;

[B]
2,支持发送HTML,支持都用户发送的邮件Package,但是需要提供SMTP服务器,用户名和密码的。
[/COLOR] [/B]



-- 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

使用道具 举报

回复
论坛徽章:
24
ITPUB元老
日期:2007-08-09 20:13:55蜘蛛蛋
日期:2012-11-18 21:00:26灰彻蛋
日期:2012-04-14 18:30:002012新春纪念徽章
日期:2012-01-04 11:49:54ITPUB十周年纪念徽章
日期:2011-11-01 16:20:282011新春纪念徽章
日期:2011-02-18 11:43:342010年世界杯参赛球队:尼日利亚
日期:2010-03-16 13:40:192009日食纪念
日期:2009-07-22 09:30:00生肖徽章2007版:虎
日期:2009-07-22 09:38:24奥运会纪念徽章:赛艇
日期:2008-10-24 13:25:17
发表于 2006-9-17 21:48 | 显示全部楼层
最初由 hphubei 发布
[B]1、最简单的,就是一次发送给一个人,并且不支持 HTML 格式的


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; [/B]


不错。。不过需要配置自己的mail 服务器吧。。。
以后有这方面需求时,找你救火啊~~

使用道具 举报

回复
论坛徽章:
8
 楼主| 发表于 2006-9-17 22:28 | 显示全部楼层
最初由 chirsdong 发布
[B]

不错。。不过需要配置自己的mail 服务器吧。。。
以后有这方面需求时,找你救火啊~~ [/B]



只需要公司有SMTP服务器即可,这个简单的,只需要提供一台SMTP服务器,不需要密码验证

使用道具 举报

回复
论坛徽章:
8
 楼主| 发表于 2006-9-17 22:30 | 显示全部楼层
最初由 chirsdong 发布
[B]

不错。。不过需要配置自己的mail 服务器吧。。。
以后有这方面需求时,找你救火啊~~ [/B]


第二种的需要提供服务器,用户名和密码验证,比这个复杂多了,等下周整理一下后再发上来。

有问题我们一起讨论了,呵呵

使用道具 举报

回复
论坛徽章:
5
ITPUB新首页上线纪念徽章
日期:2007-10-20 08:38:44奔驰
日期:2013-10-25 17:29:33
发表于 2006-9-18 14:49 | 显示全部楼层
學習

使用道具 举报

回复
论坛徽章:
12
会员2006贡献徽章
日期:2006-04-17 13:46:34ITPUB十周年纪念徽章
日期:2011-11-01 16:20:28ERP板块每日发贴之星
日期:2011-02-26 01:01:012011新春纪念徽章
日期:2011-02-18 11:43:33生肖徽章2007版:鸡
日期:2008-01-02 17:35:53ITPUB新首页上线纪念徽章
日期:2007-10-20 08:38:44会员2007贡献徽章
日期:2007-09-26 18:42:10生肖徽章:猪
日期:2006-09-07 10:15:06生肖徽章:猴
日期:2006-09-06 21:20:15世界杯纪念徽章
日期:2006-07-20 13:19:20
发表于 2006-9-18 15:43 | 显示全部楼层
学习,以前想用过,后来用workflow的notification代替了

使用道具 举报

回复
论坛徽章:
8
 楼主| 发表于 2006-9-18 15:44 | 显示全部楼层
最初由 aradin 发布
[B]学习,以前想用过,后来用workflow的notification代替了 [/B]



好像是用Alert也可以代替,没有试过,

使用道具 举报

回复
论坛徽章:
5
授权会员
日期:2006-05-13 15:05:252008新春纪念徽章
日期:2008-02-13 12:43:03生肖徽章2007版:龙
日期:2009-09-10 11:23:34ITPUB十周年纪念徽章
日期:2011-11-01 16:20:28妮可·罗宾
日期:2020-09-14 14:10:48
发表于 2006-9-19 10:26 | 显示全部楼层
關注!!

使用道具 举报

回复
论坛徽章:
0
发表于 2006-9-22 12:11 | 显示全部楼层
快点把下发送多人的也传一来吧,急需呀!
谢谢楼主了。

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 未成年人举报专区 
京ICP备16024965号-8  北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表