Oracle 10G中輕鬆發送email — UTL_MAIL

10G之前,如果要從oracle中發送email,必須藉助oracle的utl_smtp和utl_tcp功能包,自己封裝一個email發送程式,然後使用.自從10G後,這個流程將變得非常簡單.因為oracle給我們提供了一個utl_mail包,我們可以直接調用發送email.其實這個包底層也是調用了utl_smtp、utl_tcp的相關api. 不過utl_mail只能用於無安全驗證的stmp伺服器.如果smtp伺服器需要安全驗證,只能只用utl_smtp來實現.

1、確定是否安裝了utl_mail,如果沒有用下面的語句安裝utl_mail

  sqlplus sys/  SQL> @$ORACLE_HOME/rdbms/admin/utlmail.sql  SQL> @$ORACLE_HOME/rdbms/admin/prvtmail.plb

2、設定smtp_out_server參數

10g中oracle新增加了這個參數, 用來定義smtp伺服器.如果該參數沒有設定,oracle會自動解析db_domain 參數,用網域名稱來實現mail的發送,沒有db_domain也沒有設定,那麼mail將不會成功發送.建議設定smtp_out_server參數.

  alter system set smtp_out_server='mail.a.com';

如果要同時設定多個smtp伺服器,可以將各個伺服器用逗號隔開.

  alter system set smtp_out_server='mail.a.com:25,mail.b.com';

上面的網域名稱也可以用ip來代替,oracle預設會使用25連接埠來發送,也可以手動指定具體的連接埠.

  alter system set smtp_out_server='mail.a.com:25,mail.b.com:25';

3、測試郵件發送

目前utl_mail提供了三個發送郵件的過程.

 SQL> desc utl_mailPROCEDURE SEND Argument Name                  Type                    In/Out Default? ------------------------------ ----------------------- ------ -------- SENDER                         VARCHAR2                IN RECIPIENTS                     VARCHAR2                IN CC                             VARCHAR2                IN     DEFAULT BCC                            VARCHAR2                IN     DEFAULT SUBJECT                        VARCHAR2                IN     DEFAULT MESSAGE                        VARCHAR2                IN     DEFAULT MIME_TYPE                      VARCHAR2                IN     DEFAULT PRIORITY                       BINARY_INTEGER          IN     DEFAULTPROCEDURE SEND_ATTACH_RAW Argument Name                  Type                    In/Out Default? ------------------------------ ----------------------- ------ -------- SENDER                         VARCHAR2                IN RECIPIENTS                     VARCHAR2                IN CC                             VARCHAR2                IN     DEFAULT BCC                            VARCHAR2                IN     DEFAULT SUBJECT                        VARCHAR2                IN     DEFAULT MESSAGE                        VARCHAR2                IN     DEFAULT MIME_TYPE                      VARCHAR2                IN     DEFAULT PRIORITY                       BINARY_INTEGER          IN     DEFAULT ATTACHMENT                     RAW                     IN ATT_INLINE                     BOOLEAN                 IN     DEFAULT ATT_MIME_TYPE                  VARCHAR2                IN     DEFAULT ATT_FILENAME                   VARCHAR2                IN     DEFAULTPROCEDURE SEND_ATTACH_VARCHAR2 Argument Name                  Type                    In/Out Default? ------------------------------ ----------------------- ------ -------- SENDER                         VARCHAR2                IN RECIPIENTS                     VARCHAR2                IN CC                             VARCHAR2                IN     DEFAULT BCC                            VARCHAR2                IN     DEFAULT SUBJECT                        VARCHAR2                IN     DEFAULT MESSAGE                        VARCHAR2                IN     DEFAULT MIME_TYPE                      VARCHAR2                IN     DEFAULT PRIORITY                       BINARY_INTEGER          IN     DEFAULT ATTACHMENT                     VARCHAR2                IN ATT_INLINE                     BOOLEAN                 IN     DEFAULT ATT_MIME_TYPE                  VARCHAR2                IN     DEFAULT ATT_FILENAME                   VARCHAR2                IN     DEFAULT

建立存放附件的目錄.

  create directory export as '/oracle/product/dump_dir';  grant read,write on directory export to public;

沒有附件的郵件發送樣本:

begin utl_mail.send(sender=>'oracle@scmdbserver',               recipients=>'ypma@ique.com',               subject=>'oracle email test',               cc=>'gsun@ique.com,pzhang@ique.com',               bcc=>'gsun@ique.com,pzhang@ique.com',               message=>'sender ok?');end;/

帶附件的郵件發送(基於文本的附件),如要要發送二進位附件,可以使用utl_mail.send_attach_raw過程

DECLARE    fHandle utl_file.file_type;    vTextOut varchar2(32000);    text varchar2(32000);BEGIN    fHandle := UTL_FILE.FOPEN('EXPORT','a.log','r');    IF UTL_FILE.IS_OPEN(fHandle) THEN      DBMS_OUTPUT.PUT_LINE('File read open');    ELSE     DBMS_OUTPUT.PUT_LINE('File read not open');    END IF;    LOOP     begin        UTL_FILE.GET_LINE(fHandle,vTextOut);        text:=text||vTextOut;     EXCEPTION        WHEN NO_DATA_FOUND THEN EXIT;     end;     END LOOP;   UTL_FILE.FCLOSE(fHandle);   utl_mail.send_attach_varchar2(sender=>'oracle@scmdbserver',                                 recipients=>'ypma@ique.com',                                 subject=>'oracle email test',                                 cc=>'gsun@ique.com,pzhang@ique.com',                                 bcc=>'gsun@ique.com,pzhang@ique.com',                                 message=>'sender ok?',                                 attachment=>text,                                 att_filename=>'a.log');END;/

oracle在send_attach_varchar2中附件內容用varchar2來儲存,也就是說附件不能大於32k.send_attach_raw不能發送超過2000位元組的附件.

有關oracle發送mail的資訊可以參考,metalink:Doc ID:269375.1 Doc ID:201639.1 FAQ and Known Issues While Using UTL_SMTP and UTL_MAIL Doc:ID 730746.1 其中包含了發送大於32k附件的方法

 

FROM: http://www.validba.net/2009/06/oracle-10g%E4%B8%AD%E8%BD%BB%E6%9D%BE%E5%8F%91%E9%80%81email-%E5%8E%9F%E5%88%9B/

 

Ref: http://hi.baidu.com/javenzhen/blog/item/bed3b74a8f7c63e383025c77.html