Oracle發送email功能

Oracle 提供一個一個UTL_SMTP，可以發送email，結合oracle本身強大的schedule功能，比寫一隻排程效率高，且更簡單。

split功能

 /*創建package STRING_FNC

 add by milo 20170308*/

 CREATE OR REPLACE PACKAGE STRING_FNC IS

   TYPE t_array IS TABLE OF VARCHAR2(500) INDEX BY BINARY_INTEGER;

   FUNCTION SPLIT(p_in_string VARCHAR2, p_delim VARCHAR2) RETURN t_array;

 END;

 /*創建package body STRING_FNC

 add by milo 20170308*/

 CREATE OR REPLACE PACKAGE BODY STRING_FNC IS

   FUNCTION SPLIT(p_in_string VARCHAR2, p_delim VARCHAR2) RETURN t_array IS

     i       number := 0;

     pos     number := 0;

     lv_str  varchar2(500) := p_in_string;

     strings t_array;

   BEGIN

     -- determine first chuck of string

     pos := instr(lv_str, p_delim, 1, 1);

     --如果沒有拆分符號，則array第一個為p_in_string

     if pos = 0 then

       strings(1) := lv_str;

       RETURN strings;

     end if;

     -- while there are chunks left, loop

     WHILE (pos != 0) LOOP

       -- increment counter

       i := i + 1;

       -- create array element for chuck of string

       strings(i) := substr(lv_str, 1, pos - 1);

       -- remove chunk from string

       lv_str := substr(lv_str, pos + 1, length(lv_str));

       -- determine next chunk

       pos := instr(lv_str, p_delim, 1, 1);

       -- no last chunk, add to array

       IF pos = 0 THEN

         strings(i + 1) := lv_str;

       END IF;

     END LOOP;

     -- return array

     RETURN strings;

   END SPLIT;

 END;

 /

 /*

 測試功能string_fnc

 */

 declare

   str string_fnc.t_array;

 begin

   str := string_fnc.SPLIT('milo@pll***.com;even@pll***.com', ';');

   for i in 1 .. str.count loop

     dbms_output.put_line(str(i));

   end loop;

 end;

Email發送相關的設定

 /*

 add milo on 20170308

 ORA-24247: network access denied by access control list (ACL) appears next to the email and it is never sent.

 新增ACL,否則會出現以上的錯誤。

 */

 BEGIN

   -- Only uncomment the following line if ACL "network_services.xml" has already been created

   DBMS_NETWORK_ACL_ADMIN.DROP_ACL('network_services.xml');

   --新增名稱為network_services.xml

   DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(

     acl => 'network_services.xml',

     description => 'NETWORK ACL',

     principal => 'SCOTT',

     is_grant => true,

     privilege => 'connect');

   --給SCOTT加權限

   DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(

     acl => 'network_services.xml',

     principal => 'SCOTT',

     is_grant => true,

     privilege => 'resolve');

   --將ACL與spam.****.com 25關聯起來。

   DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(

     acl => 'network_services.xml',

     host => 'spam.****.com',lower_port => 25,upper_port => 25);

   COMMIT;

 END;

 /

 /*給其它賬戶（PLOEC）設置權限

 add milo on 20170308

 */

 begin

    -- Adding Connect Privilege to PLOEC

    dbms_network_acl_admin.add_privilege(

    acl => 'network_services.xml',

    principal => 'PLOEC',

    is_grant => TRUE,

    privilege => 'connect'

    );

    -- Adding Resolve Privilege to PLOEC

    dbms_network_acl_admin.add_privilege(

    acl => 'network_services.xml',

    principal => 'PLOEC',

    is_grant => TRUE,

    privilege => 'resolve'

    );

   commit;

 end;

 / 

 --測試是否可以訪問，不過預設的是80port

 select utl_http.request('spam.****.com') from dual;

/*

發送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'));

巴特西

Oracle發送email功能

最新文章

热门文章