关闭

EBS 自定义邮件通知

标签: 邮件通知EBS
1761人阅读 评论(0) 收藏 举报
分类:


EBS 自定义邮件通知


事实上Oracle 数据库本身就有提供UTL_SIMPLE(ORACLE10g

以后变成UTL_MAIL)包来实现邮件发送的功能,利用这个包可以开发出更灵活东西来。首先要安装

utl_smtp.sql、utl_tcp.sql 这两个包。


①发送带有URL 邮件代码
declare
p_sender varchar2(30) := 'cho @abc.cn';
p_recipient varchar2(30) := 'jia @abc.cn';
p_subject varchar2(50) := '使用PL/SQL 发送邮件';
p_body long := '这是邮件正文内容<a href="http://erp.oracle.cn ">进行ORACLE ERP
</a>';
mail_conn utl_smtp.connection;
mail_host varchar2(15) := 'mail.hek.cn';
user_name varchar2(156) := 'chongdong_wang@hek.cn';
user_pwd varchar2(156) := '***';
begin
--创建一个TCP MAIL 连接
mail_conn := utl_smtp.open_connection(mail_host, 25);
--ehlo 与helo 的区别:是否对邮件主机进行登陆认证
--utl_smtp.helo(main_conn,mail_host);
utl_smtp.ehlo(mail_conn, mail_host);
--登陆认证语句
utl_smtp.command(mail_conn, 'AUTH LOGIN');
--对用户及密码进行加密
utl_smtp.command(mail_conn,
demo_base64.encode(utl_raw.cast_to_raw(user_name)));
utl_smtp.command(mail_conn,
demo_base64.encode(utl_raw.cast_to_raw(user_pwd)));
--指定发件人
utl_smtp.mail(mail_conn, p_sender);
--指定收件人
utl_smtp.rcpt(mail_conn, p_recipient);
--开始写邮件内容
utl_smtp.open_data(mail_conn);

--指定显示的发件人,注意这边的显示的发件人可以上面指定发件人不同
--这实际上是SMTP 协议的缺陷,也是造成垃圾邮件主要原因
utl_smtp.write_data(mail_conn, 'From:' || p_sender || utl_tcp.CRLF);
utl_smtp.write_data(mail_conn, 'To:' || p_recipient || utl_tcp.crlf);
--邮件主题:中文必须进行编码转换,否则会乱码
utl_smtp.write_raw_data(mail_conn,
utl_raw.cast_to_raw(convert('Subject:' ||
p_subject ||
utl_tcp.CRLF,
'ZHS16GBK')));
--设置邮件内容模式为HTML,也可以直接设置文本Content-Type:text/plain
utl_smtp.write_raw_data(mail_conn,
utl_raw.cast_to_raw(convert('Content-Type:text/html;charset=GBK' ||
utl_tcp.CRLF,
'ZHS16GBK')));
utl_smtp.write_data(mail_conn, utl_tcp.CRLF);
--邮件正文
utl_smtp.write_raw_data(mail_conn,
utl_raw.cast_to_raw(convert(p_body, 'ZHS16GBK')));
--关闭连接
utl_smtp.close_data(mail_conn);
utl_smtp.quit(mail_conn);
exception
when utl_smtp.transient_error or utl_smtp.permanent_error then
utl_smtp.quit(mail_conn);
raise_application_error(-20000, sqlerrm);
when others then
raise_application_error(-20001, 'The send mail was error ' || sqlerrm);
end;

②发送带有附件的邮件
declare
p_sender varchar2(30) := '*****@he.cn';
p_recipient varchar2(30) := '******* @he.cn';
p_subject varchar2(50) := 'PL/SQL 发邮件、带链接、带附件';
p_body long := 'PL/SQL 发邮件、带链接带附件<br>这是邮件正文内容啦!这是带附件的啦!<
br></p><a href="http://erp.home.cn">进行ORACLE ERP</a>';
mail_conn utl_smtp.connection;
mail_host varchar2(15) := '*******.cn';
user_name varchar2(156) := '*******.cn';
user_pwd varchar2(156) := '*********k';
--发附件要用到的变量
L_FIL BFILE;
L_FILE_LEN NUMBER;
L_MODULO NUMBER;
L_PIECES NUMBER;
L_FILE_HANDLE UTL_FILE.FILE_TYPE;

L_AMT BINARY_INTEGER:=672*3;/* ensures proper format; 2016 */
L_FILEPOS PLS_INTEGER:=1;/* pointer for the file */
L_CHUNKS NUMBER;
L_BUF RAW(2100);
L_DATA RAW(2100);
L_MAX_LINE_WIDTH NUMBER:=54;
L_LINE VARCHAR2(1000);
L_MESG VARCHAR2(32767);
BOUNDARY CONSTANT VARCHAR2(256) := '-----7D81B75CCC90D2974F7A1CBD';
FIRST_BOUNDARY CONSTANT VARCHAR2(256) := '--' || BOUNDARY || utl_tcp.CRLF;
LAST_BOUNDARY CONSTANT VARCHAR2(256) := '--' || BOUNDARY || '--' || utl_tcp.CRLF;
--发送带有附件邮件,MIME 必须设为multipart/mixed
MULTIPART_MIME_TYPE CONSTANT VARCHAR2(256) := 'multipart/mixed; boundary="'|| BOUNDARY
|| '"';
begin
--创建一个TCP MAIL 连接
mail_conn := utl_smtp.open_connection(mail_host, 25);
--ehlo 与helo 的区别:是否对邮件主机进行登陆认证
--utl_smtp.helo(main_conn,mail_host);
utl_smtp.ehlo(mail_conn, mail_host);
--登陆认证语句
utl_smtp.command(mail_conn, 'AUTH LOGIN');
--对用户及密码进行加密
utl_smtp.command(mail_conn,
demo_base64.encode(utl_raw.cast_to_raw(user_name)));
utl_smtp.command(mail_conn,
demo_base64.encode(utl_raw.cast_to_raw(user_pwd)));
--指定发件人
utl_smtp.mail(mail_conn, p_sender);
--指定收件人
utl_smtp.rcpt(mail_conn, p_recipient);
--开始写邮件内容
utl_smtp.open_data(mail_conn);
utl_smtp.write_data(mail_conn, 'From:' || p_sender || utl_tcp.CRLF);
utl_smtp.write_data(mail_conn, 'To:' || p_recipient || utl_tcp.crlf);
utl_smtp.write_data(mail_conn, 'Subject: ' || p_subject || utl_tcp.crlf);
--中文编码转换
utl_smtp.write_raw_data(mail_conn,utl_raw.cast_to_raw(convert('Subject:' ||p_subject
||utl_tcp.CRLF,'ZHS16GBK')));
utl_smtp.write_raw_data(mail_conn,utl_raw.cast_to_raw(convert('Content-Type:'||MULTIPART_MIME_TYPE||ut
l_tcp.CRLF,'ZHS16GBK')));
--utl_tcp.CRLF 数据流行尾符
utl_smtp.write_data(mail_conn, utl_tcp.CRLF);

--邮件正文
utl_smtp.write_data(mail_CONN, FIRST_BOUNDARY);
utl_smtp.write_raw_data(mail_conn,utl_raw.cast_to_raw(convert('Content-Type:text/html;charset=GB2312'
||utl_tcp.CRLF, 'ZHS16GBK')));
utl_smtp.write_data(mail_conn, utl_tcp.CRLF);
utl_smtp.write_raw_data(mail_conn,utl_raw.cast_to_raw(convert(p_body, 'ZHS16GBK')));
utl_smtp.write_data(mail_conn, utl_tcp.CRLF);
--附件格式
utl_smtp.write_data(mail_CONN, FIRST_BOUNDARY);
utl_smtp.write_raw_data(mail_conn,utl_raw.cast_to_raw(convert('Content-Type:text/html;charset=GB2312'
||utl_tcp.CRLF, 'ZHS16GBK')));
utl_smtp.WRITE_RAW_DATA(mail_conn, UTL_RAW.CAST_TO_RAW(CONVERT('Content-Disposition' ||
':' ||'attachment;filename="'||'qq.xls"' || utl_tcp.CRLF, 'ZHS16GBK')));
utl_smtp.write_raw_data(mail_conn,utl_raw.cast_to_raw(convert('Content-Transfer-Encoding:base64'||utl_tcp.CR
LF,'ZHS16GBK')));
utl_smtp.write_data(mail_CONN, UTL_TCP.CRLF);
--附件二进制流
BEGIN
--把附件分成多份,这样可以发送超过32K 的附件
L_FILEPOS := 1;
--CREATE OR REPLACE DIRECTORY U_FIEL AS '/data/book/'
--qq.xls 附件放在ORACLE 服务器/data/book/下,注意大写
L_FIL := BFILENAME('U_FIEL', 'qq.xls');
L_FILE_LEN := DBMS_LOB.GETLENGTH(L_FIL);
L_MODULO := MOD(L_FILE_LEN, L_AMT);
L_PIECES := TRUNC(L_FILE_LEN / L_AMT);
IF (L_MODULO <> 0) THEN
L_PIECES := L_PIECES + 1;
END IF;
DBMS_LOB.FILEOPEN(L_FIL, DBMS_LOB.FILE_READONLY);
DBMS_LOB.READ(L_FIL, L_AMT, L_FILEPOS, L_BUF);
L_DATA := NULL;
FOR I IN 1 .. L_PIECES LOOP
L_FILEPOS := I * L_AMT + 1;
L_FILE_LEN := L_FILE_LEN - L_AMT;
L_DATA := UTL_RAW.CONCAT(L_DATA, L_BUF);
L_CHUNKS := TRUNC(UTL_RAW.LENGTH(L_DATA) / L_MAX_LINE_WIDTH);
IF (I <> L_PIECES) THEN
L_CHUNKS := L_CHUNKS - 1;
END IF;
utl_smtp.write_raw_data(MAIL_CONN, UTL_ENCODE.BASE64_ENCODE(L_DATA));

L_DATA := NULL;
IF (L_FILE_LEN < L_AMT AND L_FILE_LEN > 0) THEN
L_AMT := L_FILE_LEN;
END IF;
DBMS_LOB.READ(L_FIL, L_AMT, L_FILEPOS, L_BUF);
END LOOP;
DBMS_LOB.FILECLOSE(L_FIL);
EXCEPTION
WHEN OTHERS THEN
DBMS_LOB.FILECLOSE(L_FIL);
utl_smtp.WRITE_DATA(mail_CONN, UTL_TCP.CRLF);
RAISE;
END; --结束处理二进制附件
--关闭连接
utl_smtp.close_data(mail_conn);
utl_smtp.quit(mail_conn);
exception
when utl_smtp.transient_error or utl_smtp.permanent_error then
utl_smtp.quit(mail_conn);
raise_application_error(-20000, sqlerrm);
when others then
utl_smtp.quit(mail_conn);
DBMS_OUTPUT.put_line(sqlerrm);
end;




0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:211500次
    • 积分:3534
    • 等级:
    • 排名:第9686名
    • 原创:152篇
    • 转载:6篇
    • 译文:0篇
    • 评论:3条
    最新评论