利用ORACLE utl_stmp 包實現 mail傳送功能

利用ORACLE utl_stmp 包實現 mail傳送功能

資料倉儲的etl過程會涉及到很多的儲存程，因為源資料的多樣性，且etl過程大都在夜間無人值守的情況下執行，對於採用手工etl方式的資料倉儲環境，需要一種及時快捷的etl過程執行狀況通知，可以有2中方式實現

一：在每個儲存過程的exception段或者錯誤資訊日誌表中增加trigger，通過utl_stmp包傳送郵件通知。

二：mail監控方式需要我們主動的去收發郵件，才能知道etl執行狀況，有一定的侷限性，現在行動通訊的高度發達，使我們可以用一種更方便更快捷的方式，利用sms簡訊通知，為此，需要企業需要花點小錢，增加簡訊服務平臺，在etl的時候，通過儲存過程，把每個錯誤資訊傳送到簡訊平臺中，然後通過簡訊平臺和行動通訊服務商的連線通道傳送到手機上，這樣，你就可以帶著一部手機，走到任何地方，都可以及時知道etl的狀況了。

 

下面簡單的介紹一下，如何利用utl_stmp包實現mail傳送

 

實現最簡單的mai功能，參見 utl_smtp 包的說明例子，列出需要如下8個步驟

1:l_mail_conn := utl_smtp.open_connection(l_mailhost, 25);

--定義一個stmp連線，指向mail伺服器
2: utl_smtp.helo(l_mail_conn, l_mailhost);

--建立連線
3:  utl_smtp.mail(l_mail_conn, p_sender);

--設定發件人地址
  4: utl_smtp.rcpt(l_mail_conn, p_recipient);

--設定郵件人地址
  5: utl_smtp.open_data(l_mail_conn);

--開啟預定義的指向smtp的連線通道
  6:utl_smtp.write_data(l_mail_conn, p_message);

--寫入郵件正文內容
  7:utl_smtp.close_data(l_mail_conn);

--關閉smtp連線通道
  8: utl_smtp.quit(l_mail_conn);

--退出

上面這些就可以實現一個簡單maile功能，不還不實用，不支援中文，不支援郵件密碼驗證，下面給一個實用的mail 指令碼。

 

CREATE OR REPLACE PROCEDURE send_mail(
                p_sender   In Varchar2,
                p_receiver IN VARCHAR2, 
                p_message   IN VARCHAR2) 
AS 
  mailhost  VARCHAR2(100) := '10.103.41.181';        --smtp mail 伺服器 
  mail_conn utl_smtp.connection; 
  PROCEDURE send_header(conn  IN OUT NOCOPY utl_smtp.connection,  
                        name  IN VARCHAR2,  
                        value IN VARCHAR2)
   As  
   BEGIN  
     utl_smtp.write_data(conn,name || ': =?GB2312?B?' ||      --注意使用GB2312才能支援中文 
     utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(value))) || '?='|| utl_tcp.crlf);   
   END;  
  --p_user Varchar2(30) :='hfklj';
  --p_pass Varchar2(20) :='';
BEGIN 
  mail_conn :=utl_smtp.open_connection(mailhost,25); 
  utl_smtp.helo(mail_conn,mailhost); 
 --UTL_SMTP.COMMAND(mail_conn, 'AUTH LOGIN');
 --utl_smtp.command(mail_conn, utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(p_user))));
 --utl_smtp.command(mail_conn, utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(p_pass))));     
  utl_smtp.mail(mail_conn,p_sender);    -- sender 
  utl_smtp.rcpt(mail_conn,p_receiver); -- receiver 
  utl_smtp.open_data(mail_conn); 
  send_header(mail_conn,'From',p_sender);--顯示 mail中的發件人
  send_header(mail_conn,'To', p_receiver);  --顯示mail中的 收件人 
  --send_header(mail_conn,'Cc', 'hfklj@pec.com.cn');--顯示抄送人地址
  --send_header(mail_conn,'Bcc', 'hfklj5@pec.com.cn');--顯示祕送人地址
  send_header(mail_conn,'Subject', 'ETL 報錯郵件');  --郵件主題 
  UTL_SMTP.write_data(mail_conn, 'Date: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || Chr(13)|| chr(10)); 
  utl_smtp.write_data(mail_conn, 'MIME-Version: 1.0' || chr(13) || chr(10)); 
  utl_smtp.write_data(mail_conn, 'Content-type: text/plain;Charset=UTF8;' || chr(13) || chr(10)); 
  utl_smtp.write_data(mail_conn, 'Content-Transfer-Encoding: base64' || chr(13) || chr(10)); 
  utl_smtp.write_data(mail_conn, chr(13) || chr(10));   
  utl_smtp.write_data(mail_conn, utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(p_message)))); 
  utl_smtp.close_data(mail_conn); 
  utl_smtp.quit(mail_conn); 
EXCEPTION 
  WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN 
    utl_smtp.quit(mail_conn); 
    raise_application_error(-20000, 
      'Failed tosend mail due to the following error: ' || sqlerrm); 
  WHEN OTHERS THEN 
    raise_application_error(-20001, 
      'The following error has occured: ' || sqlerrm);    
End send_mail;

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/10159839/viewspace-232370/，如需轉載，請註明出處，否則將追究法律責任。