- 创建 ACL
BEGIN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(acl => 'email_server_permissions.xml',
description => 'Enables network permissions for the e-mail server',
principal => 'LJZ',
is_grant => TRUE,
privilege => 'connect');
END;
-- 与邮件服务关联
BEGIN
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(acl => 'email_server_permissions.xml',
host => 'smtp.163.com',
lower_port => 25,
upper_port => NULL);
END;
-- 这样 email_user 用户帐户创建的存储过程便可以向此邮件服务器发送邮件
--删除
BEGIN
DBMS_NETWORK_ACL_ADMIN.drop_acl(acl => 'email_server_permissions.xml');
COMMIT;
END;
--查询
SELECT host, lower_port, upper_port, acl FROM dba_network_acls;
SELECT acl,
principal,
privilege,
is_grant,
TO_CHAR(start_date, 'DD-MON-YYYY') AS start_date,
TO_CHAR(end_date, 'DD-MON-YYYY') AS end_date
FROM dba_network_acl_privileges;
存储过程
create or replace procedure p_send_mail(p_from in varchar2,
p_to in varchar2,
p_subject in varchar2,
p_text in clob default null,
p_html in clob default null,
p_attachment_path varchar2,
p_smtp_hostname in varchar2,
p_smtp_portnum in varchar2 default 25,
p_need_valid varchar2 default 'Y',
p_user_name varchar2,
p_user_pwd varchar2) is
/*
作用：用oracle发送邮件
主要功能：1、支持多收件人。
2、支持中文
3、支持抄送人
4、支持大于32K的附件
5、支持多行正文
6、支持多附件
7、支持HTML邮件
作者：HH
参数说明：
p_from 发件人
p_to 收件人，多收件人用逗号或分号分隔
p_subject 主题
p_text 文本内容
p_html HTML内容
p_attachment_path 附件地址（绝对路径）,多附件用逗号或分号分隔
p_smtp_hostname 邮件服务器地址 例：smtp.163.com
p_smtp_portnum 端口号，默认25
p_need_valid 是否需要用户名密码验证，默认需要（'Y'）
p_user_name 用户名
p_user_pwd 密码
*/
l_crlf varchar2(2) := utl_tcp.crlf;
l_write_encode varchar2(20) := 'zhs16gbk';
l_attachment_encode varchar2(10) := 'base64';
l_attachment_mime_type varchar2(50) := 'application/octet-stream';
l_attachment_disposition varchar2(10) := 'attachment';
l_boundary_mail varchar2(255) default '#---hh***hh-mail---#';
l_boundary_content varchar2(255) default '#---hh***hh-content---#';
l_first_boundary constant varchar2(256) := '--' || l_boundary_mail ||
l_crlf;
l_last_boundary constant varchar2(256) := '--' || l_boundary_mail || '--' ||
l_crlf;
l_connection utl_smtp.connection;
l_body_html clob := empty_clob; --HTML邮件内容
l_offset number;
l_ammount number;
l_temp varchar2(32767) default null;
l_file_handle utl_file.file_type;
l_line varchar2(1000);
l_mesg varchar2(32767);
l_filepos pls_integer := 1;
l_fil bfile;
l_file_len number;
l_modulo number;
l_pieces number;
l_amt number := 8580;
l_chunks number;
l_buf raw(32767);
l_data raw(32767);
l_max_line_width number := 54;
l_directory_base_name varchar2(100) := 'DIR_FOR_SEND_MAIL';
l_receivers varchar2(32767);
l_count number;
type address_list is table of varchar2(100) index by binary_integer;
my_address_list address_list;
type acct_list is table of varchar2(100) index by binary_integer;
my_acct_list acct_list;
--分割邮件地址或者附件地址
procedure sub_splite_str(p_str varchar2, p_splite_flag int default 1) is
l_addr varchar2(254) := '';
l_len int;
l_str varchar2(4000);
j int := 0; --表示邮件地址或者附件的个数
begin
/*处理接收邮件地址列表，包括去空格、将;转换为,等*/
l_str := trim(rtrim(replace(replace(p_str, ';', ','), ' ', ''), ','));
l_len := length(l_str);
for i in 1 .. l_len loop
if substr(l_str, i, 1) <> ',' then
l_addr := l_addr || substr(l_str, i, 1);
else
j := j + 1;
if p_splite_flag = 1 then
--表示处理邮件地址
--前后需要加上'<>'，否则很多邮箱将不能发送邮件
l_addr := '<' || l_addr || '>';
--调用邮件发送过程
my_address_list(j) := l_addr;
elsif p_splite_flag = 2 then
--表示处理附件名称
my_acct_list(j) := l_addr;
end if;
l_addr := '';
end if;
if i = l_len then
j := j + 1;
if p_splite_flag = 1 then
--调用邮件发送过程
l_addr := '<' || l_addr || '>';
my_address_list(j) := l_addr;
elsif p_splite_flag = 2 then
my_acct_list(j) := l_addr;
end if;
end if;
end loop;
end; --删除directory
procedure sub_drop_directory(p_directory_name varchar2) is
begin
select count(1)
into l_count
from dba_directories
where directory_name = upper(p_directory_name);
if l_count > 0 then
execute immediate 'drop directory ' || p_directory_name;
end if;
exception
when others then
--null;
raise;
end; --创建directory
procedure sub_create_directory(p_directory_name varchar2, p_dir varchar2) is
begin
execute immediate 'create directory ' || p_directory_name || ' as ''' ||
p_dir || '''';
execute immediate 'grant read,write on directory ' || p_directory_name ||
' to public';
exception
when others then
raise;
end; --返回附件源文件所在目录或者名称
function sub_get_file(p_file varchar2, p_get int) return varchar2 is
--p_get=1 表示返回目录
--p_get=2 表示返回文件名
l_file varchar2(1000);
begin
if instr(p_file, '\') > 0 then
if p_get = 1 then
l_file := substr(p_file, 1, instr(p_file, '\', -1) - 1) || '\';
elsif p_get = 2 then
l_file := substr(p_file,
- (length(p_file) - instr(p_file, '\', -1)));
end if;
elsif instr(p_file, '/') > 0 then
if p_get = 1 then
l_file := substr(p_file, 1, instr(p_file, '/', -1) - 1);
elsif p_get = 2 then
l_file := substr(p_file,
- (length(p_file) - instr(p_file, '/', -1)));
end if;
end if;
return l_file;
end; --发送附件
procedure sub_attachment(conn in out nocopy utl_smtp.connection,
filename in varchar2,
dt_name in varchar2) is
l_filename varchar2(1000);
l_amount number;
begin
sub_drop_directory(dt_name);
--创建directory
sub_create_directory(dt_name, sub_get_file(filename, 1));
--得到附件文件名称
l_filename := sub_get_file(filename, 2);
l_temp := l_temp || l_crlf || '--' || l_boundary_mail || l_crlf;
l_temp := l_temp || 'Content-Type: ' || l_attachment_mime_type || ';
name="' || l_filename || '"
Content-Transfer-Encoding: ' || l_attachment_encode || '
Content-Disposition: ' || l_attachment_disposition || ';
filename="' || l_filename || '"' || l_crlf || l_crlf;
utl_smtp.write_raw_data(l_connection,
utl_raw.cast_to_raw(convert(l_temp,
l_write_encode)));
--begin
--begin
--把附件分成多份，这样可以发送超过32k的附件
l_filepos := 1; --重置offset，在发送多个附件时，必须重置
l_fil := bfilename(dt_name, l_filename);
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);
l_data := null;
l_amount := l_amt;
for i in 1 .. l_pieces loop
dbms_lob.read(l_fil, l_amount, l_filepos, l_buf);
l_filepos := i * l_amount + 1;
l_file_len := l_file_len - l_amount;
utl_smtp.write_raw_data(conn, utl_encode.base64_encode(l_buf));
if i = l_pieces then
l_amount := l_file_len;
end if;
end loop;
dbms_lob.fileclose(l_fil);
/*exception
when others then
dbms_lob.fileclose(l_fil);
sub_end_boundary(conn);
raise;
end; --结束处理二进制附件*/
sub_drop_directory(dt_name);
end; --结束过程attachment
procedure sub_send_mail is
l_from varchar2(1000) := '<' || p_from || '>';
begin
l_connection := utl_smtp.open_connection(p_smtp_hostname,
p_smtp_portnum);
utl_smtp.helo(l_connection, p_smtp_hostname);
/* smtp服务器登录校验 */
if p_need_valid = 'Y' 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_user_name))));
utl_smtp.command(l_connection,
utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(p_user_pwd))));
end if;
utl_smtp.mail(l_connection, l_from);
sub_splite_str(p_to); --处理邮件地址
for k in 1 .. my_address_list.count loop
l_receivers := l_receivers || my_address_list(k) || ';';
utl_smtp.rcpt(l_connection, my_address_list(k));
end loop;
l_temp := l_temp || 'From: ' || l_from || l_crlf;
l_temp := l_temp || 'To: ' || l_receivers || l_crlf;
--l_temp := l_temp || 'Cc: ' || l_receivers || l_crlf;--抄送
--l_temp := l_temp || 'Bcc: ' || l_receivers || l_crlf;--密送
l_temp := l_temp || 'Subject: ' || p_subject || l_crlf;
--l_temp := l_temp || 'X-Mailer: Foxmail 7, 1, 3, 48[cn]' || l_crlf;--发送客户端
l_temp := l_temp || 'Content-Type: multipart/mixed; boundary="' ||
l_boundary_mail || '"' || l_crlf;
l_temp := l_temp || 'MIME-Version: 1.0' || l_crlf || l_crlf;
l_temp := l_temp || '--' || l_boundary_mail || l_crlf;
if nvl(p_attachment_path, ' ') <> ' ' then
l_temp := l_temp || 'content-type: multipart/alternative; boundary="' ||
l_boundary_content || '"' || l_crlf || l_crlf || l_crlf;
l_temp := l_temp || '--' || l_boundary_content || l_crlf;
end if;
--开始
dbms_lob.createtemporary(l_body_html, false, 10);
dbms_lob.write(l_body_html, length(l_temp), 1, l_temp);
--文本内容
l_offset := dbms_lob.getlength(l_body_html) + 1;
l_temp := 'content-type: text/plain; charset="GB2312"; Content-Transfer-Encoding: base64' ||
l_crlf || l_crlf;
dbms_lob.write(l_body_html, length(l_temp), l_offset, l_temp);
if trim(p_text) is not null then
dbms_lob.append(l_body_html, p_text);
end if;
--html内容
if nvl(p_attachment_path, ' ') <> ' ' then
l_temp := l_crlf || l_crlf || '--' || l_boundary_content ||'--' || l_crlf;
else
l_temp := l_crlf || l_crlf || '--' || l_boundary_mail ||'--' || l_crlf;
end if;
l_temp := l_temp ||
'content-type: text/html;charset="GB2312";Content-Transfer-Encoding: quoted-printable' ||
l_crlf || l_crlf;
l_offset := dbms_lob.getlength(l_body_html) + 1;
dbms_lob.write(l_body_html, length(l_temp), l_offset, l_temp);
dbms_lob.append(l_body_html, nvl(p_html, ' '));
--content结束
if nvl(p_attachment_path, ' ') <> ' ' then
l_temp := l_crlf || l_crlf || '--' || l_boundary_content || '--' ||
l_crlf || l_crlf;
else
l_temp := l_crlf || '--' || l_boundary_mail || '--' || l_crlf ||
l_crlf;
end if;
l_offset := dbms_lob.getlength(l_body_html) + 1;
dbms_lob.write(l_body_html, length(l_temp), l_offset, l_temp);
--写入邮件
l_offset := 1;
l_ammount := 1900;
utl_smtp.open_data(l_connection);
while l_offset < dbms_lob.getlength(l_body_html) loop
utl_smtp.write_raw_data(l_connection,
utl_raw.cast_to_raw(convert(dbms_lob.substr(l_body_html,
l_ammount,
l_offset),
l_write_encode)));
l_offset := l_offset + l_ammount;
l_ammount := least(1900, dbms_lob.getlength(l_body_html) - l_ammount);
end loop;
commit;
----------------------------------------------------
l_temp := null;
--附件
--如果文件名称不为空，则发送附件
if (p_attachment_path is not null) then
--根据逗号或者分号拆分附件地址
sub_splite_str(p_attachment_path, 2);
--循环发送附件(在同一个邮件中)
for k in 1 .. my_acct_list.count loop
sub_attachment(conn => l_connection,
filename => my_acct_list(k),
dt_name => l_directory_base_name || to_char(k));
l_temp := l_crlf;
end loop;
end if;
l_temp := l_crlf || l_crlf || '--' || l_boundary_mail || '--' || l_crlf ||
l_crlf;
utl_smtp.write_raw_data(l_connection,
utl_raw.cast_to_raw(convert(l_temp,
l_write_encode)));
commit;
utl_smtp.close_data(l_connection);
utl_smtp.quit(l_connection);
--utl_smtp.close_connection(l_connection);
dbms_lob.freetemporary(l_body_html);
end; begin
sub_send_mail();
/*exception
when others then
null;*/
end;