Oracle 11g 環境下，利用utl_smtp建立發送郵件的預存程序

標籤：oracle   utl_smtp   database   ora-29278   

網上太多發郵件儲存過程，我就不轉寄了，弄個簡單的作為樣本；

create or replace procedure Send_mail(mail_body varchar2) is  smtp_conn  utl_smtp.connection;  user_name  varchar2(20) := utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw('[email protected]')));   user_paswd varchar2(20) := utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw('password')));  lv_mail_header varchar2(200):='From:[email protected]'||utl_tcp.CRLF||                                'To:[email protected]'||utl_tcp.CRLF||                                'Subject:Oracle資料庫'||utl_tcp.CRLF;  lv_mail_content varchar2(2000);begin  lv_mail_content := utl_tcp.CRLF||mail_body;    smtp_conn := utl_smtp.open_connection('smtp.email.com',25);  utl_smtp.helo(smtp_conn,'smtp.email.com');  utl_smtp.command(smtp_conn,'AUTH LOGIN');  utl_smtp.command(smtp_conn,user_name); --郵件使用者名  utl_smtp.command(smtp_conn,user_paswd); --郵件密碼  utl_smtp.mail(smtp_conn,'<[email protected]>'); --寄件者郵箱  utl_smtp.rcpt(smtp_conn,'<[email protected]>'); --收件者郵箱  utl_smtp.open_data(smtp_conn);  utl_smtp.write_data(smtp_conn,utl_raw.cast_to_raw(lv_mail_header));   utl_smtp.write_raw_data(smtp_conn,utl_raw.cast_to_raw(lv_mail_content));  utl_smtp.close_data(smtp_conn);  utl_smtp.quit(smtp_conn);exception  when others then    utl_smtp.quit(smtp_conn);end Send_mail;/--儲存過程已建立

第二步直接測試函數；

begin  send_mail('測試內容');end;/ORA-29278: SMTP 臨時性錯誤: 421 Service not availableORA-06512: 在 "SYS.UTL_SMTP", line 21ORA-06512: 在 "SYS.UTL_SMTP", line 97ORA-06512: 在 "SYS.UTL_SMTP", line 139ORA-06512: 在 "SYS.UTL_MAIL", line 405ORA-06512: 在 "SYS.UTL_MAIL", line 594ORA-06512: 在 line 2

話說我第一次看到這個錯誤非常震驚，因為整個郵件發送的預存程序是先用PL/SQL直接測試代碼後，再封裝到預存程序中的，後來經過搜尋才知道為了更細緻地控制網路許可權,Oracle 11g中針對UTL_TCP， UTL_SMTP， UTL_MAIL， UTL_HTTP和 UTL_INADDR的訪問設定了單獨的許可權存取控制方式（ACL）.

OK，第三步，設定ACL；

--ACL第一步，建立BEGIN          dbms_network_acl_admin.create_acl(acl         => 'httprequestpermission.xml',  --檔案名稱，可以任意取名                                          DESCRIPTION => 'Normal Access',                                            principal   => 'CONNECT',  --角色                                          is_grant    => TRUE,                                            PRIVILEGE   => 'connect',                                            start_date  => NULL,                                            end_date    => NULL);  END;  /commit; --必須要提交；

然後檢查是否建立了該 ACL控制檔案；

SQL> SELECT any_path FROM resource_view WHERE any_path like '/sys/acls/%.xml';

如果列表裡出現剛才建立的檔案httprequestpermission.xml，請繼續ACL第二步

--ACL第二步，授權使用者（樣本用scott作為測試）begin          dbms_network_acl_admin.add_privilege(acl        => 'httprequestpermission.xml',                                               principal  => 'SCOTT',  --使用者，請按照實際變更                                             is_grant   => TRUE,                                               privilege  => 'connect',                                               start_date => null,                                               end_date   => null);  end;  /

--ACL第三步，添加主機或網域名稱    begin          dbms_network_acl_admin.assign_acl(acl        => 'httprequestpermission.xml',                                            host       => 'www.baidu.com',  --http網頁地址                                          lower_port => 80,  --http連接埠                                          upper_port => NULL);      end;  /commit;    begin          dbms_network_acl_admin.assign_acl(acl        => 'httprequestpermission.xml',                                            host       => 'smtp.sina.com.cn',  --smtp伺服器位址                                          lower_port => 25,  --smtp連接埠                                          upper_port => NULL);      end;  /commit;

最後就是再次測試預存程序

SQL> begin  2    send_mail(mail_body => 'afafagaga');  3  end;  4  / PL/SQL procedure successfully completed

沒有任何錯誤，郵件正確收到；（本文測試環境：Oracle 11.2.0.0，OS：Windows 2008 Server）

（作者測試過程中曾遇到過

ORA-24247: 網路訪問被存取控制清單 (ACL) 拒絕;

ORA-29278: SMTP 臨時性錯誤: 421 Service not available；

ORA-44416: ACL 無效: 無法解析的主使用者 ‘AGENT‘

這三個主要錯誤，按照上述步驟均能解決這些問題）