EBS自定义邮件通知
前面已经介绍过了EBS预警的功能，事实上Oracle数据库本身就有提供UTL_SIMPLE(ORACLE10g以后变成UTL_MAIL)包来实现邮件发送的功能，利用这个包可以开发出更灵活东西来。首先要安装utlsmtp.sql、utltcp.sql这两个包。
①发送带有URL邮件代码
declare
p_sender varchar2(30) := 'chongdong_wang@hek.cn';
p_recipient varchar2(30) := 'jian_li2@hek.cn';
p_subject varchar2(50) := '使用PL/SQL发送邮件';
p_body long := '这是邮件正文内容啦!我又来啦!!!进行ORACLE ERP';
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) := 'metalink@hek.cn';
p_recipient varchar2(30) := 'chongdong_wang@hek.cn';
p_subject varchar2(50) := 'PL/SQL发邮件、带链接、带附件';
p_body long := 'PL/SQL发邮件、带链接带附件
这是邮件正文内容啦!这是带附件的啦!!
进行ORACLE ERP';
mail_conn utl_smtp.connection;
mail_host varchar2(15) := 'mail.hek.cn';
user_name varchar2(156) := 'metalink@hek.cn';
user_pwd varchar2(156) := 'metalink'; --发附件要用到的变量
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||utl_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.CRLF,'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;
