- 创建 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;