Oracle發送email功能
Oracle 提供一個一個UTL_SMTP，可以發送email，結合oracle本身強大的schedule功能，比寫一隻排程效率高，且更簡單。
split功能
1 /*創建package STRING_FNC 2 add by milo 20170308*/ 3 CREATE OR REPLACE PACKAGE STRING_FNC IS 4 TYPE t_array IS TABLE OF VARCHAR2(500) INDEX BY BINARY_INTEGER; 5 FUNCTION SPLIT(p_in_string VARCHAR2, p_delim VARCHAR2) RETURN t_array; 6 END; 7 8 /*創建package body STRING_FNC 9 add by milo 20170308*/ 10 CREATE OR REPLACE PACKAGE BODY STRING_FNC IS 11 FUNCTION SPLIT(p_in_string VARCHAR2, p_delim VARCHAR2) RETURN t_array IS 12 i number := 0; 13 pos number := 0; 14 lv_str varchar2(500) := p_in_string; 15 strings t_array; 16 BEGIN 17 -- determine first chuck of string 18 pos := instr(lv_str, p_delim, 1, 1); 19 20 --如果沒有拆分符號，則array第一個為p_in_string 21 if pos = 0 then 22 strings(1) := lv_str; 23 RETURN strings; 24 end if; 25 26 -- while there are chunks left, loop 27 WHILE (pos != 0) LOOP 28 -- increment counter 29 i := i + 1; 30 -- create array element for chuck of string 31 strings(i) := substr(lv_str, 1, pos - 1); 32 -- remove chunk from string 33 lv_str := substr(lv_str, pos + 1, length(lv_str)); 34 -- determine next chunk 35 pos := instr(lv_str, p_delim, 1, 1); 36 -- no last chunk, add to array 37 IF pos = 0 THEN 38 strings(i + 1) := lv_str; 39 END IF; 40 END LOOP; 41 -- return array 42 RETURN strings; 43 END SPLIT; 44 END; 45 / 46 47 /* 48 測試功能string_fnc 49 */ 50 declare 51 str string_fnc.t_array; 52 begin 53 str := string_fnc.SPLIT('milo@pll***.com;even@pll***.com', ';'); 54 for i in 1 .. str.count loop 55 dbms_output.put_line(str(i)); 56 end loop; 57 end;
Email發送相關的設定
1 /* 2 add milo on 20170308 3 ORA-24247: network access denied by access control list (ACL) appears next to the email and it is never sent. 4 新增ACL,否則會出現以上的錯誤。 5 */ 6 BEGIN 7 -- Only uncomment the following line if ACL "network_services.xml" has already been created 8 DBMS_NETWORK_ACL_ADMIN.DROP_ACL('network_services.xml'); 9 10 --新增名稱為network_services.xml 11 DBMS_NETWORK_ACL_ADMIN.CREATE_ACL( 12 acl => 'network_services.xml', 13 description => 'NETWORK ACL', 14 principal => 'SCOTT', 15 is_grant => true, 16 privilege => 'connect'); 17 18 --給SCOTT加權限 19 DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE( 20 acl => 'network_services.xml', 21 principal => 'SCOTT', 22 is_grant => true, 23 privilege => 'resolve'); 24 25 --將ACL與spam.****.com 25關聯起來。 26 DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL( 27 acl => 'network_services.xml', 28 host => 'spam.****.com',lower_port => 25,upper_port => 25); 29 30 COMMIT; 31 32 END; 33 / 34 35 /*給其它賬戶（PLOEC）設置權限 36 add milo on 20170308 37 */ 38 begin 39 -- Adding Connect Privilege to PLOEC 40 dbms_network_acl_admin.add_privilege( 41 acl => 'network_services.xml', 42 principal => 'PLOEC', 43 is_grant => TRUE, 44 privilege => 'connect' 45 ); 46 -- Adding Resolve Privilege to PLOEC 47 dbms_network_acl_admin.add_privilege( 48 acl => 'network_services.xml', 49 principal => 'PLOEC', 50 is_grant => TRUE, 51 privilege => 'resolve' 52 ); 53 commit; 54 end; 55 / 56 57 58 --測試是否可以訪問，不過預設的是80port 59 select utl_http.request('spam.****.com') from dual; 60
/* 發送email功能 written by milo 2017-03-08 */ CREATE OR REPLACE PROCEDURE send_mail(p_to IN VARCHAR2,--可以多個接收人，例如 milo@pllink.com;others@pllink.com p_from IN VARCHAR2,--發送人 p_subject IN VARCHAR2,--email主旨 p_text_msg IN VARCHAR2 DEFAULT NULL, p_html_msg IN VARCHAR2 DEFAULT NULL,--email之HTML內容 p_smtp_host IN VARCHAR2,--伺服器網域或者IP p_account IN VARCHAR2,--郵箱賬號 p_password IN VARCHAR2,--郵箱登錄密碼 p_smtp_port IN NUMBER DEFAULT 25) AS --預設port為25 l_mail_conn UTL_SMTP.connection; l_boundary VARCHAR2(50) := '----=*#abc1234321cba#*='; v_email_recipient_list string_fnc.t_array; BEGIN l_mail_conn := UTL_SMTP.open_connection(p_smtp_host, p_smtp_port); --UTL_SMTP.helo(l_mail_conn, p_smtp_host); UTL_SMTP.ehlo(l_mail_conn, p_smtp_host); --驗證用戶 UTL_SMTP.command(l_mail_conn, 'AUTH LOGIN'); --需要改用UTL_RAW.cast_to_varchar2，否則會有錯誤：ORA-29279 smtp 535 5.7.3 Authentication unsuccessful --UTL_SMTP.command(l_mail_conn,utl_encode.base64_encode(utl_raw.cast_to_raw(p_account))); --UTL_SMTP.command(l_mail_conn,utl_encode.base64_encode(utl_raw.cast_to_raw(p_password))); UTL_SMTP.command(l_mail_conn, UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(UTL_RAW.cast_to_raw(p_account)))); UTL_SMTP.command(l_mail_conn, UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(UTL_RAW.cast_to_raw(p_password)))); UTL_SMTP.mail(l_mail_conn, p_from); --將多組接收人按;拆分成Array v_email_recipient_list := string_fnc.SPLIT(p_to, ';'); for i in 1 .. v_email_recipient_list.count loop --dbms_output.put_line(v_email_recipient_list(i)); --分別添加收件人 UTL_SMTP.rcpt(l_mail_conn, v_email_recipient_list(i)); end loop; UTL_SMTP.open_data(l_mail_conn); UTL_SMTP.write_data(l_mail_conn, 'Date: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || UTL_TCP.crlf); UTL_SMTP.write_data(l_mail_conn, 'To: ' || p_to || UTL_TCP.crlf); UTL_SMTP.write_data(l_mail_conn, 'From: ' || p_from || UTL_TCP.crlf); UTL_SMTP.write_data(l_mail_conn, 'Subject: ' || p_subject || UTL_TCP.crlf); UTL_SMTP.write_data(l_mail_conn, 'Reply-To: ' || p_from || UTL_TCP.crlf); UTL_SMTP.write_data(l_mail_conn, 'MIME-Version: 1.0' || UTL_TCP.crlf); UTL_SMTP.write_data(l_mail_conn, 'Content-Type: multipart/alternative; boundary="' || l_boundary || '"' || UTL_TCP.crlf || UTL_TCP.crlf); IF p_text_msg IS NOT NULL THEN UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || UTL_TCP.crlf); UTL_SMTP.write_data(l_mail_conn, 'Content-Type: text/plain; charset="uft-8"' || UTL_TCP.crlf || UTL_TCP.crlf); UTL_SMTP.write_data(l_mail_conn, p_text_msg); UTL_SMTP.write_data(l_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf); END IF; IF p_html_msg IS NOT NULL THEN UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || UTL_TCP.crlf); UTL_SMTP.write_data(l_mail_conn, 'Content-Type: text/html; charset="utf-8"' || UTL_TCP.crlf || UTL_TCP.crlf); --UTL_SMTP.write_data(l_mail_conn, p_html_msg); --解決中文亂碼問題 UTL_SMTP.write_raw_data(l_mail_conn, utl_raw.cast_to_raw(UTL_TCP.CRLF || p_html_msg || UTL_TCP.CRLF)); UTL_SMTP.write_data(l_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf); END IF; UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || '--' || UTL_TCP.crlf); UTL_SMTP.close_data(l_mail_conn); UTL_SMTP.quit(l_mail_conn); END; /
/*僅作send_mail測試*/ DECLARE l_html VARCHAR2(32767); BEGIN l_html := '<html> <head> <title>Test HTML message</title> </head> <body> <p>This is a <b>HTML</b> <i>version</i> of the test message.</p> <p><img src="http://oracle-base.com/images/site_logo.gif" alt="Site Logo" /> </body> </html>'; send_mail(p_to => 'shipping@***.com;even@***.com', p_from => 'milo@***.com', p_subject => 'Test Message', p_text_msg => 'This is a test message.', p_html_msg => l_html, p_smtp_host => 'spam.***.com', p_account => 'milo@***.com', p_password => '***'); END; /
更新：
a. 支援寄送人的别名显示,直接用正则分组取邮件地址，例如 "Milo Xie" <milo@pllink.com>
2017/3/14
--UTL_SMTP.mail(l_mail_conn, p_from); UTL_SMTP.mail(l_mail_conn, REGEXP_REPLACE(p_from, '(.*)([<])(.*)([>])', '\3'));
