ebs邮件推送

84 阅读 0 评论 56 点赞

我是靠谱客的博主含糊芝麻，最近开发中收集的这篇文章主要介绍ebs邮件推送，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

CREATE OR REPLACE PACKAGE send_mail_test IS
TYPE attachment_rec IS RECORD(
file_name VARCHAR2(240),
file_content CLOB);

TYPE attachment_tbl IS TABLE OF attachment_rec INDEX BY BINARY_INTEGER;

FUNCTION get_address(p_addr_list IN OUT VARCHAR2) RETURN VARCHAR2;
PROCEDURE set_mail(p_host IN VARCHAR2, --邮件服务器IP地址
p_port IN PLS_INTEGER DEFAULT 25, --服务器端口
p_tx_timeout IN PLS_INTEGER DEFAULT NULL, --超时时间
p_sender IN VARCHAR2, --邮件发送者
p_password IN VARCHAR2, --发件人密码
p_receiver IN VARCHAR2, --接收者用;隔开
p_cc IN VARCHAR2 DEFAULT NULL, --抄送
p_bcc IN VARCHAR2 DEFAULT NULL, --暗抄送
p_subject IN VARCHAR2,
p_main_body IN CLOB, --正文
p_html_body IN CLOB, --表格
p_attachment_tbl IN attachment_tbl, --附件
p_charset IN VARCHAR2 DEFAULT 'ZHS16GBK');

END send_mail_test;

CREATE OR REPLACE PACKAGE BODY send_mail_test IS
PROCEDURE read_clob(p_connection IN OUT utl_smtp.connection,
p_clob IN CLOB) IS
l_off INTEGER := 1;
l_amt INTEGER := 4096;
l_str VARCHAR2(4096);
BEGIN
--循环读取clob 数据
BEGIN
LOOP
dbms_lob.read(p_clob, l_amt, l_off, l_str); --将p_clob 数据从 l_off位置取出l_amt长度,放入 l_str 中
l_off := l_off + l_amt; --移动位置
utl_smtp.write_raw_data(p_connection,
utl_raw.cast_to_raw(convert(l_str ||
utl_tcp.crlf,
'ZHS16GBK')));
END LOOP;

EXCEPTION
WHEN no_data_found THEN
NULL;
END;

END read_clob;
FUNCTION get_address(p_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;
END IF;
IF (c = '"') THEN
inside_quote := TRUE;
END IF;
IF (instr(chrs, c) >= 1) THEN
RETURN i;
END IF;
i := i + 1;
END LOOP;
RETURN 0;
END;

BEGIN
p_addr_list := ltrim(p_addr_list);
i := lookup_unquoted_char(p_addr_list, ',;');
IF (i >= 1) THEN
addr := substr(p_addr_list, 1, i - 1);
p_addr_list := substr(p_addr_list, i + 1);
ELSE
addr := p_addr_list;
p_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 || '>'; --address add <> 20131129
END;

PROCEDURE main IS
BEGIN
NULL;
/* open_conn(p_host => '10.73.50.7',
p_sender => 'sfmro@cqsf.com',
p_password => 'mro123456');*/

END main;

PROCEDURE set_mail(p_host IN VARCHAR2, --邮件服务器IP地址
p_port IN PLS_INTEGER DEFAULT 25, --服务器端口
p_tx_timeout IN PLS_INTEGER DEFAULT NULL, --超时时间
p_sender IN VARCHAR2, --邮件发送者
p_password IN VARCHAR2, --发件人密码
p_receiver IN VARCHAR2, --接收者用;隔开
p_cc IN VARCHAR2 DEFAULT NULL, --抄送
p_bcc IN VARCHAR2 DEFAULT NULL, --暗抄送
p_subject IN VARCHAR2, --标题
p_main_body IN CLOB, --正文
p_html_body IN CLOB, --表格
p_attachment_tbl IN attachment_tbl, --附件
p_charset IN VARCHAR2 DEFAULT 'ZHS16GBK') IS

l_connection utl_smtp.connection;
l_boundary VARCHAR2(240) := '----=_NextPart_59B63A7B_0BA38AB0_79851240';
l_boundary1 VARCHAR2(240) := '----=_NextPart_59B63A7B_0BA38AB0_1F3E04CA';
l_receiver VARCHAR2(240) := NULL;
l_cc VARCHAR2(240) := NULL;
l_bcc VARCHAR2(240) := NULL;

BEGIN

l_connection := utl_smtp.open_connection(host => p_host, --outlook SMTP服务器的域名/IP地址
port => p_port, --默认端口25
tx_timeout => p_tx_timeout --设置超时时间 3秒
);

utl_smtp.helo(c => l_connection, domain => p_host); --HELO命令执行与SMTP服务器的初始握手。
IF p_password IS NOT NULL THEN
utl_smtp.command(l_connection, 'AUTH LOGIN');
utl_smtp.command(l_connection,
utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(p_sender))));
utl_smtp.command(l_connection,
utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(p_password))));
END IF;
utl_smtp.mail(l_connection, '<' || p_sender || '>');

l_receiver := REPLACE(p_receiver, ' ', NULL); --去掉空格
l_cc := REPLACE(p_cc, ' ', NULL);
l_bcc := REPLACE(p_bcc, ' ', NULL);

WHILE l_receiver IS NOT NULL LOOP
utl_smtp.rcpt(l_connection, send_mail_test.get_address(l_receiver));
END LOOP;

WHILE l_cc IS NOT NULL LOOP
utl_smtp.rcpt(l_connection, send_mail_test.get_address(l_cc));
END LOOP;

WHILE l_bcc IS NOT NULL LOOP
utl_smtp.rcpt(l_connection, send_mail_test.get_address(l_bcc));
END LOOP;

utl_smtp.open_data(l_connection);

utl_smtp.write_raw_data(l_connection,
utl_raw.cast_to_raw(convert('From' || ': ' ||
p_sender ||
utl_tcp.crlf,
p_charset))); --发件人

utl_smtp.write_raw_data(l_connection,
utl_raw.cast_to_raw(convert('To' || ': ' ||
l_receiver ||
utl_tcp.crlf,
p_charset))); --收件人 CC BCC
IF p_cc IS NOT NULL THEN
utl_smtp.write_raw_data(l_connection,
utl_raw.cast_to_raw(convert('Cc' || ': ' || l_cc ||
utl_tcp.crlf,
p_charset))); --收件人 CC BCC
END IF;

IF p_bcc IS NOT NULL THEN
utl_smtp.write_raw_data(l_connection,
utl_raw.cast_to_raw(convert('Bcc' || ': ' ||
l_bcc ||
utl_tcp.crlf,
p_charset))); --收件人 CC BCC

END IF;

utl_smtp.write_raw_data(l_connection,
utl_raw.cast_to_raw(convert('Subject' || ': ' ||
p_subject ||
utl_tcp.crlf,
p_charset))); --邮件主题

utl_smtp.write_data(l_connection,
'Content-Type: multipart/mixed;' || utl_tcp.crlf ||
' boundary="' || l_boundary || '"' || utl_tcp.crlf);
utl_smtp.write_data(l_connection,
'Content-Transfer-Encoding: base64' || utl_tcp.crlf);
utl_smtp.write_data(l_connection, utl_tcp.crlf);
utl_smtp.write_data(l_connection, '--' || l_boundary || utl_tcp.crlf);

utl_smtp.write_data(l_connection,
'Content-Type: multipart/alternative;' ||
utl_tcp.crlf || ' boundary="' || l_boundary1 || '"' ||
utl_tcp.crlf);
utl_smtp.write_data(l_connection, utl_tcp.crlf);
utl_smtp.write_data(l_connection, '--' || l_boundary1 || utl_tcp.crlf);

utl_smtp.write_data(l_connection,
'Content-Type: text/html' || utl_tcp.crlf ||
'charset=GB2312' ||
'Content-Transfer-Encoding: base64' || utl_tcp.crlf);

utl_smtp.write_data(l_connection, utl_tcp.crlf);
--循环读取正文
read_clob(l_connection, p_main_body);

utl_smtp.write_data(l_connection, utl_tcp.crlf);
read_clob(l_connection, p_html_body);

utl_smtp.write_data(l_connection, utl_tcp.crlf);

utl_smtp.write_data(l_connection,
'--' || l_boundary1 || '--' || utl_tcp.crlf);

IF p_attachment_tbl.count <> 0 THEN
FOR i IN p_attachment_tbl.first .. p_attachment_tbl.last LOOP
utl_smtp.write_data(l_connection, utl_tcp.crlf);

utl_smtp.write_data(l_connection,
'--' || l_boundary || utl_tcp.crlf);

utl_smtp.write_raw_data(l_connection,
utl_raw.cast_to_raw(convert('Content-Disposition' || ':' ||
'attachment;filename="' || p_attachment_tbl(i)
.file_name || '"' ||
utl_tcp.crlf,
'ZHS16GBK')));

utl_smtp.write_data(l_connection, utl_tcp.crlf);

read_clob(l_connection, p_attachment_tbl(i).file_content);
/* utl_smtp.write_raw_data(l_connection,
utl_raw.cast_to_raw(convert(p_attachment_tbl(i)
.file_content ||
utl_tcp.crlf,
'ZHS16GBK')));*/
END LOOP;
END IF;
utl_smtp.write_data(l_connection, utl_tcp.crlf);

utl_smtp.write_data(l_connection,
'--' || l_boundary || '--' || utl_tcp.crlf);

utl_smtp.close_data(l_connection); --结束DATA命令
utl_smtp.quit(l_connection); --断开连接

END;

END send_mail_test;

修改了字符集，以及read clob错误

CREATE OR REPLACE PACKAGE h3c_mail_pub IS
TYPE attachment_rec IS RECORD(
file_name VARCHAR2(240),
file_content CLOB);

TYPE attachment_tbl IS TABLE OF attachment_rec INDEX BY BINARY_INTEGER;

FUNCTION get_address(p_addr_list IN OUT VARCHAR2) RETURN VARCHAR2;
PROCEDURE set_mail(p_host IN VARCHAR2, --邮件服务器IP地址
p_port IN PLS_INTEGER DEFAULT 25, --服务器端口
p_tx_timeout IN PLS_INTEGER DEFAULT NULL, --超时时间
p_sender IN VARCHAR2, --邮件发送者
p_password IN VARCHAR2, --发件人密码
p_receiver IN VARCHAR2, --接收者用;隔开
p_cc IN VARCHAR2 DEFAULT NULL, --抄送
p_bcc IN VARCHAR2 DEFAULT NULL, --暗抄送
p_subject IN VARCHAR2,
p_main_body IN CLOB, --正文
p_html_body IN CLOB, --表格
p_attachment_tbl IN attachment_tbl, --附件
p_charset IN VARCHAR2 DEFAULT 'AL32UTF8');

END h3c_mail_pub;
/

CREATE OR REPLACE PACKAGE BODY h3c_mail_pub IS
g_charset VARCHAR2(240);
PROCEDURE read_clob(p_connection IN OUT NOCOPY utl_smtp.connection,
p_clob IN CLOB) IS
l_off INTEGER := 1;
l_amt INTEGER := 4096;
l_str VARCHAR2(32767);
BEGIN
--循环读取clob 数据
IF p_clob IS NULL THEN
RETURN;
END IF;
BEGIN
LOOP
dbms_lob.read(p_clob, l_amt, l_off, l_str); --将p_clob 数据从 l_off位置取出l_amt长度,放入 l_str 中
l_off := l_off + l_amt; --移动位置
utl_smtp.write_raw_data(p_connection,
utl_raw.cast_to_raw(convert(l_str ||
utl_tcp.crlf,
g_charset)));
END LOOP;

EXCEPTION
WHEN no_data_found THEN
NULL;
END;

END read_clob;
FUNCTION get_address(p_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;
END IF;
IF (c = '"') THEN
inside_quote := TRUE;
END IF;
IF (instr(chrs, c) >= 1) THEN
RETURN i;
END IF;
i := i + 1;
END LOOP;
RETURN 0;
END;

BEGIN
p_addr_list := ltrim(p_addr_list);
i := lookup_unquoted_char(p_addr_list, ',;');
IF (i >= 1) THEN
addr := substr(p_addr_list, 1, i - 1);
p_addr_list := substr(p_addr_list, i + 1);
ELSE
addr := p_addr_list;
p_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 || '>'; --address add <> 20131129
END;

PROCEDURE set_mail(p_host IN VARCHAR2, --邮件服务器IP地址
p_port IN PLS_INTEGER DEFAULT 25, --服务器端口
p_tx_timeout IN PLS_INTEGER DEFAULT NULL, --超时时间
p_sender IN VARCHAR2, --邮件发送者
p_password IN VARCHAR2, --发件人密码
p_receiver IN VARCHAR2, --接收者用;隔开
p_cc IN VARCHAR2 DEFAULT NULL, --抄送
p_bcc IN VARCHAR2 DEFAULT NULL, --暗抄送
p_subject IN VARCHAR2, --标题
p_main_body IN CLOB, --正文
p_html_body IN CLOB, --表格
p_attachment_tbl IN attachment_tbl, --附件
p_charset IN VARCHAR2 DEFAULT 'AL32UTF8') IS

l_connection utl_smtp.connection;
l_boundary VARCHAR2(240) := '----=_NextPart_59B63A7B_0BA38AB0_79851240';
l_boundary1 VARCHAR2(240) := '----=_NextPart_59B63A7B_0BA38AB0_1F3E04CA';
l_receiver VARCHAR2(240) := NULL;
l_cc VARCHAR2(240) := NULL;
l_bcc VARCHAR2(240) := NULL;

BEGIN
g_charset := nvl(p_charset, 'AL32UTF8');
l_connection := utl_smtp.open_connection(host => p_host, --outlook SMTP服务器的域名/IP地址
port => p_port, --默认端口25
tx_timeout => p_tx_timeout --设置超时时间 3秒
);

utl_smtp.helo(c => l_connection, domain => p_host); --HELO命令执行与SMTP服务器的初始握手。
IF p_password IS NOT NULL THEN
utl_smtp.command(l_connection, 'AUTH LOGIN');
utl_smtp.command(l_connection,
utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(p_sender))));
utl_smtp.command(l_connection,
utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(p_password))));
END IF;
utl_smtp.mail(l_connection, '<' || p_sender || '>');

l_receiver := REPLACE(p_receiver, ' ', NULL); --去掉空格
l_cc := REPLACE(p_cc, ' ', NULL);
l_bcc := REPLACE(p_bcc, ' ', NULL);

WHILE l_receiver IS NOT NULL LOOP
utl_smtp.rcpt(l_connection, h3c_mail_pub.get_address(l_receiver));
END LOOP;

WHILE l_cc IS NOT NULL LOOP
utl_smtp.rcpt(l_connection, h3c_mail_pub.get_address(l_cc));
END LOOP;

WHILE l_bcc IS NOT NULL LOOP
utl_smtp.rcpt(l_connection, h3c_mail_pub.get_address(l_bcc));
END LOOP;

utl_smtp.open_data(l_connection);

utl_smtp.write_raw_data(l_connection,
utl_raw.cast_to_raw(convert('From' || ': ' ||
p_sender ||
utl_tcp.crlf,
p_charset))); --发件人

utl_smtp.write_raw_data(l_connection,
utl_raw.cast_to_raw(convert('To' || ': ' ||
l_receiver ||
utl_tcp.crlf,
p_charset))); --收件人 CC BCC
IF p_cc IS NOT NULL THEN
utl_smtp.write_raw_data(l_connection,
utl_raw.cast_to_raw(convert('Cc' || ': ' || l_cc ||
utl_tcp.crlf,
p_charset))); --收件人 CC BCC
END IF;

IF p_bcc IS NOT NULL THEN
utl_smtp.write_raw_data(l_connection,
utl_raw.cast_to_raw(convert('Bcc' || ': ' ||
l_bcc ||
utl_tcp.crlf,
p_charset))); --收件人 CC BCC

END IF;

utl_smtp.write_raw_data(l_connection,
utl_raw.cast_to_raw(convert('Subject' || ': ' ||
p_subject ||
utl_tcp.crlf,
p_charset))); --邮件主题

utl_smtp.write_data(l_connection,
'Content-Type: multipart/mixed;' || utl_tcp.crlf ||
' boundary="' || l_boundary || '"' || utl_tcp.crlf);
utl_smtp.write_data(l_connection,
'Content-Transfer-Encoding: base64' || utl_tcp.crlf);
utl_smtp.write_data(l_connection, utl_tcp.crlf);
utl_smtp.write_data(l_connection, '--' || l_boundary || utl_tcp.crlf);

utl_smtp.write_data(l_connection,
'Content-Type: multipart/alternative;' ||
utl_tcp.crlf || ' boundary="' || l_boundary1 || '"' ||
utl_tcp.crlf);
utl_smtp.write_data(l_connection, utl_tcp.crlf);
utl_smtp.write_data(l_connection, '--' || l_boundary1 || utl_tcp.crlf);

utl_smtp.write_data(l_connection,
'Content-Type: text/html' || utl_tcp.crlf ||
'charset=UTF-8' ||
'Content-Transfer-Encoding: base64' || utl_tcp.crlf);

utl_smtp.write_data(l_connection, utl_tcp.crlf);
--循环读取正文
read_clob(l_connection, p_main_body);

utl_smtp.write_data(l_connection, utl_tcp.crlf);
read_clob(l_connection, p_html_body);

utl_smtp.write_data(l_connection, utl_tcp.crlf);

utl_smtp.write_data(l_connection,
'--' || l_boundary1 || '--' || utl_tcp.crlf);

IF p_attachment_tbl.count <> 0 THEN
FOR i IN p_attachment_tbl.first .. p_attachment_tbl.last LOOP
utl_smtp.write_data(l_connection, utl_tcp.crlf);

utl_smtp.write_data(l_connection,
'--' || l_boundary || utl_tcp.crlf);

utl_smtp.write_raw_data(l_connection,
utl_raw.cast_to_raw(convert('Content-Disposition' || ':' ||
'attachment;filename="' || p_attachment_tbl(i).file_name || '"' ||
utl_tcp.crlf,
g_charset)));

utl_smtp.write_data(l_connection, utl_tcp.crlf);

read_clob(l_connection, p_attachment_tbl(i).file_content);
/* utl_smtp.write_raw_data(l_connection,
utl_raw.cast_to_raw(convert(p_attachment_tbl(i)
.file_content ||
utl_tcp.crlf,
'ZHS16GBK')));*/
END LOOP;
END IF;
utl_smtp.write_data(l_connection, utl_tcp.crlf);

utl_smtp.write_data(l_connection,
'--' || l_boundary || '--' || utl_tcp.crlf);

utl_smtp.close_data(l_connection); --结束DATA命令
utl_smtp.quit(l_connection); --断开连接

END;