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

來源:互聯網
上載者:User

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

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

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('username@email.com')));   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:username@email.com'||utl_tcp.CRLF||                                'To:sanoul@email.com'||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,'<username@email.com>'); --寄件者郵箱  utl_smtp.rcpt(smtp_conn,'<sanoul@email.com>'); --收件者郵箱  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'

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


怎使用utl_smtp從Oracle中寄送電子郵件 (1)

(注釋:如果你的應用程式使構建在Oracle 8i的基礎之上,前提是運行維護在Oracle 10g或以上版本,那麼你也可以通過舊的utl_smtp工具包來寄送電子郵件。) utl_smtp代碼的一個優點就是可以在Oracle 10g上正常運行,所以我們不需要用utl_mail來替代utl_smtp工具包。雖然utl_mail完全替代utl_smtp的一天勿庸置疑會降臨,不過現在utl_smtp還能夠滿足我們的需求。 首先,確認utl_smtp工具包已經安裝在你的系統裡(當然是在SYS架構裡)。如果你還沒有安裝這個工具包,可以在你的ORACLE_HOME\RDBMS\admin檔案夾中找到utlsmtp.sql指令碼。你還需要utl_tcp包;同樣的,如果你發現utl_tcp包還沒有載入,也可以從跟utlsmtp.sql指令碼相同的路徑找到utltcp.sql指令碼。最後,你還需要知道你的企業SMTP伺服器的URL。(注意,下面的例子不適用於對SMTP進行了安全設定的伺服器,如Gmail) 程式包說明語句: 以下是引用片段: create or replace PACKAGE sendmail IS procedure send (p_sender varchar2, p_recipient varchar2, p_subject varchar2, p_body varchar2 default null); end sendmail;仔細觀察以上語句的主體和下面的程式包,你可能會發現send的公用模式(public method)依賴於被稱為common的私人模式(private method),這樣以後我們可以對這個程式包進行擴充,並為大家展示怎樣發送二進位大對象(blob)附件。例如,如果你產生了一份PDF文檔,並把它儲存在你的資料庫裡,你可以會想要把它作為電子郵件的附件發送出去, common模式就是用來完成這個任務的。將要用到的代碼來自於基本send模式和send_blob模式。 以下就是程式包的主體: 以下是引用片段: create or replace PACKAGE BODY sendmail IS procedure common (p_sender varchar2, p_recipient varchar2, p_subject varchar2, c out utl_smtp.connection) is v_recipient varchar2(1000); begin --make connection to smtp c := utl_smtp.open_connection('smtp.example.com'); --identify the domain of the sender utl_smtp.helo(c, 'example.com'); --start a mail, specify the sender utl_smtp.mail(c, p_sender); --identify recipient utl_smtp.rcpt(c, v_recipient); --start the mail body utl_smtp.open_data(c); utl_smtp.write_data(c, 'From: ' || p_sender || utl_tcp.crlf); utl_smtp.write_data(c, 'To: &#39......餘下全文>>
 
怎用預存程序發郵件

------------------------------------------------寫郵件標頭和郵件內容------------------------------------------
PROCEDURE WRITE_DATA(P_CONN IN OUT NOCOPY UTL_SMTP.CONNECTION,
P_NAME IN VARCHAR2,
P_VALUE IN VARCHAR2,
P_SPLITE VARCHAR2 DEFAULT ':',
P_CRLF VARCHAR2 DEFAULT L_CRLF) IS
BEGIN
/* utl_raw.cast_to_raw 對解決中文亂碼問題很重要*/
UTL_SMTP.WRITE_RAW_DATA(P_CONN, UTL_RAW.CAST_TO_RAW(CONVERT(P_NAME ||
P_SPLITE ||
P_VALUE ||
P_CRLF, 'ZHS16GBK')));
END;
----------------------------------------寫MIME郵件尾部-----------------------------------------------------

PROCEDURE END_BOUNDARY(CONN IN OUT NOCOPY UTL_SMTP.CONNECTION,
LAST IN BOOLEAN DEFAULT FALSE) IS
BEGIN
UTL_SMTP.WRITE_DATA(CONN, UTL_TCP.CRLF);
IF (LAST) THEN
UTL_SMTP.WRITE_DATA(CONN, LAST_BOUNDARY);
END IF;
END;

----------------------------------------------發送附件----------------------------------------------------

PROCEDURE ATTACHMENT(CONN IN OUT NOCOPY UTL_SMTP.CONNECTION,
MIME_TYPE IN VARCHAR2 DEFAULT 'text/plain',
INLINE IN BOOLEAN DEFAULT TRUE,
FILENAME ......餘下全文>>
 

相關文章

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.