Oracle 發郵件過程

oracle 版本 11g

 CREATE OR REPLACE PROCEDURE pro_sendmail(sender     IN VARCHAR2 DEFAULT '發件人郵箱',
                                              recipient1 IN VARCHAR2 DEFAULT '收件人郵箱',
                                              recipient2 IN VARCHAR2 DEFAULT '收件人郵箱',
                                              recipient3 IN VARCHAR2 DEFAULT '收件人郵箱',
                                              recipient4 IN VARCHAR2 DEFAULT '收件人郵箱',
                                              subject    IN VARCHAR2 DEFAULT '標題',
                                              message    IN VARCHAR2 DEFAULT '郵件內容',
                                              message01    IN VARCHAR2 DEFAULT '郵件內容') IS
   mailhost VARCHAR2(30) := 'smtp.139.com'; --郵箱伺服器地址我這裡139郵箱
   --receiver VARCHAR2(30) :='監控管理員';
   mail_passwd varchar(20) :='郵箱密碼';
   connter_addr        utl_smtp.connection;
   msg_content      VARCHAR2(1000);
BEGIN
   msg_content := 'Date: ' || to_char(SYSDATE , 'yyyy-mm-dd hh24:mi:ss') ||
          utl_tcp.crlf || 'From: ' || utl_tcp.crlf ||
          'subject: ' || subject || utl_tcp.crlf || 'To:           '>;;' || utl_tcp.crlf || 'Cc:           recipient4 || '>' || utl_tcp.crlf || '' || utl_tcp.crlf ||
          message||message01;
   connter_addr   := utl_smtp.open_connection(mailhost, 25);
   utl_smtp.helo(connter_addr, mailhost);
   utl_smtp.command(connter_addr, 'auth login');
   utl_smtp.command(connter_addr,
                    utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(sender))));
   utl_smtp.command(connter_addr,
                    utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(mail_passwd))));
   utl_smtp.mail(connter_addr, sender);      --發件人
   utl_smtp.rcpt(connter_addr, recipient1);  --收件人
   utl_smtp.rcpt(connter_addr, recipient2);
   utl_smtp.rcpt(connter_addr, recipient3);
   utl_smtp.rcpt(connter_addr, recipient4);

  utl_smtp.open_data(connter_addr);          --開啟郵箱連線
   utl_smtp.write_raw_data(connter_addr, utl_raw.cast_to_raw(msg_content));
   utl_smtp.close_data(connter_addr);
   utl_smtp.quit(connter_addr);
   EXCEPTION  
 
    WHEN OTHERS THEN
         DBMS_OUTPUT.put_line(DBMS_UTILITY.format_error_stack);  
         DBMS_OUTPUT.put_line(DBMS_UTILITY.format_call_stack);   
END pro_sendmail;
 
新增ACL規則
 1）建立訪問控制列表acl

 BEGIN
    DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
                                                                      acl          => 'mail server.xml',
                                                                      description  => ' mail server',
                                                                      principal    => 'SYS',--進行操作的資料庫使用者且使用者要大寫
                                                                      is_grant     => TRUE,
                                                                      privilege    => 'connect');
END;

2）將ACL與郵件伺服器關聯（這裡我用139郵箱）

 /*新浪的郵箱伺服器地址，如果是用qq作為傳送郵件，
smtp.139.com  139郵箱
 smtp.163.com  163郵箱
smtp.126.com  126郵箱
smtp.sina.com 新浪郵箱則用qq郵箱伺服器地址smtp.qq .com*/

BEGIN
DBMS_NETWORK_ACL_ADMIN.assign_acl (
                                                             acl         => 'mail server.xml',
                                                             host        => 'smtp.139.com',
                                                             lower_port  => 25,
                                                             upper_port  => NULL);
  COMMIT;
END;

，可用dba_network_acls檢視是否已經生成

 
SELECT host, lower_port, upper_port, acl FROM sys.dba_network_acls;
 

 
 3）為執行的資料庫使用者授予連線郵件伺服器的許可權

 

 
BEGIN
  dbms_network_acl_admin.add_privilege(
                                                           acl        => 'mail server.xml',
                                                           principal =>'SYS',--進行操作的資料庫使用者且使用者要大寫                                                                        

                                                           is_grant   =>  TRUE,
                                                           privilege  => 'connect');
END;

 採用dba_network_acl_privileges檢視檢視授予的許可權

 
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 sys.dba_network_acl_privileges;
 

如果配置規則檔案有錯誤可以執行下面語句重新配置
-- 刪除acl配置檔案


    
begin
  dbms_network_acl_admin.drop_acl(
    'mail server.xml'
  );
  commit;
 end;

 4.utl_tcp.crlf作用：換行

注意在寫郵件標題，傳送人，接收人，郵件內容時，各個之間一定要有換行，否則會無法傳送或者是郵件內容為空。

 

5.中文亂碼問題

    utl_smtp.write_raw_data 用該過程替代utl_smtp.write_data可以解決中文亂碼的問題。