Oracle 預存程序中發送郵件，並支援使用者驗證、中文標題和內容

 在 Oracle
的預存程序執行中，我們可能希望它本身能完成郵件發送執行的結果，特別是在捕獲到了異常時。不能總是依賴於調用預存程序的外部程式－－調用後，根據出口參
數，發送執行結果。這一需求更迫切的表現在非人工參與的 Oracle Job 調用預存程序的情況下。

所幸，Oracle
為我們提供了發送郵件的工具包 UTL_SMTP，它最早出現在 Oracle 8.1.7
版本中。下面是我從網路上搜尋相關資料後、綜合整理、多處修正、數次調試、排除萬難而寫出的一個發送郵件的預存程序。可支援需使用者驗證的郵件伺服器，中文
標題和中文內容無亂碼，只還未支援附件的發送，相信這方面應用較少，需要的話再 Google 一下，且文後參考中有相應的連結。

view source

print
?01.
CREATE
OR
REPLACE
PROCEDURE
send_mail(

02.
     
p_recipient VARCHAR2,
-- 郵件接收人

03.
     
p_subject   VARCHAR2,
-- 郵件標題

04.
     
p_message   VARCHAR2 
-- 郵件內文

05.
)

06.
IS

07.
 
08.
    
--下面四個變數請根據實際郵件伺服器進行賦值

09.
    
v_mailhost  VARCHAR2(30) :=
'mail.xxx.com'
;    
--SMTP伺服器位址

10.
    
v_user      VARCHAR2(30) :=
'user'
;
--登入SMTP伺服器的使用者名稱

11.
    
v_pass      VARCHAR2(20) :=
'pass'
;         
--登入SMTP伺服器的密碼

12.
    
v_sender    VARCHAR2(50)  :=
'user@xxx.com'
;
--發送都郵箱，一般與 ps_user 對應

13.
    
 
14.
    
v_conn  UTL_SMTP.
connection
;
--到郵件伺服器的串連

15.
    
v_msg varchar2(4000); 
--郵件內容

16.
 
17.
BEGIN

18.
 
19.
    
v_conn := UTL_SMTP.open_connection(v_mailhost, 25);

20.
    
UTL_SMTP.ehlo(v_conn, v_mailhost);
--是用 ehlo() 而不是 helo() 函數

21.
    
--否則會報：ORA-29279: SMTP 永久性錯誤: 503 5.5.2 Send hello first.

22.
 
23.
    
UTL_SMTP.command(v_conn,
'AUTH LOGIN'
);  
-- smtp伺服器登入校正

24.
    
UTL_SMTP.command(v_conn,UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(UTL_RAW.cast_to_raw(v_user))));

25.
    
UTL_SMTP.command(v_conn,UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(UTL_RAW.cast_to_raw(v_pass))));

26.
 
27.
    
UTL_SMTP.mail(v_conn, v_sender);    
--設定寄件者

28.
    
UTL_SMTP.rcpt(v_conn, p_recipient); 
--設定收件者

29.
 
30.
    
-- 建立要發送的郵件內容 注意前序資訊和郵件內文之間要空一行

31.
    
v_msg :=
'Date:'
|| TO_CHAR(SYSDATE,
'dd mon yy hh24:mi:ss'
)

32.
        
|| UTL_TCP.CRLF ||
'From: '
|| v_sender ||
'<'
|| v_sender ||
'>'

33.
        
|| UTL_TCP.CRLF ||
'To: '
  || p_recipient ||
'<'
|| p_recipient ||
'>'

34.
        
|| UTL_TCP.CRLF ||
'Subject: '
|| p_subject

35.
        
|| UTL_TCP.CRLF || UTL_TCP.CRLF 
-- 這前面是前序資訊

36.
        
|| p_message;   
-- 這個是郵件內文

37.
 
38.
    
UTL_SMTP.open_data(v_conn);
--開啟流

39.
    
UTL_SMTP.write_raw_data(v_conn, UTL_RAW.cast_to_raw(v_msg));
--這樣寫標題和內容都能用中文

40.
    
UTL_SMTP.close_data(v_conn);
--關閉流

41.
    
UTL_SMTP.quit(v_conn);
--關閉串連

42.
    
 
43.
EXCEPTION

44.
 
45.
    
WHEN
OTHERS
THEN

46.
        
DBMS_OUTPUT.put_line(DBMS_UTILITY.format_error_stack);

47.
        
DBMS_OUTPUT.put_line(DBMS_UTILITY.format_call_stack);

48.
 
49.
END
send_mail;

上面代碼在 Oracle 9.2.0(Solaris 平台，資料庫字元集是 ZHS16GBK) 中實際運行後通過，在 PL/SQL Developer 的 SQL Window 中用下面代碼調用該預存程序：

begin
    send_mail(
'fantasia@sina.com','

中文標題','中文內容');
end;


而且在 PL/SQL Developer 的 Command Windows 或是 SQL *Plus 中執行：

send_mail(
'fantasia@sina.com','

中文標題','中文內容');

都能正常發送，成功收到郵件，並且標題和內容的中文顯示正常。

網路上直接拿下來的例子，多是沒有很好的解決中文文題，有些不能支援郵件伺服器的驗證，當今時代要找個不需要使用者驗證的郵件伺服器太難了。關鍵是有個致命問題是，一運行就報類似如下的錯誤：

ORA-29279: SMTP 永久性錯誤: 503 5.5.2 Send hello first.

----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
38298bd60        45  procedure TCSM.SEND_MAIL
38a4efa40         2  anonymous block

原因是：若郵件伺服器需要使用者驗證時，對郵件伺服器打招呼的方式不對，不能寫成
 
UTL_SMTP.helo(v_conn, v_mailhost);


而要寫成:

UTL_SMTP.ehlo(v_conn, v_mailhost);


如果你的郵件中只用英文，那麼上面代碼中的三行：

UTL_SMTP.open_data(v_conn); --開啟流
UTL_SMTP.write_raw_data(v_conn, UTL_RAW.cast_to_raw(v_msg));
UTL_SMTP.close_data(v_conn); --關閉流


只需要寫成一行就行了，如下：

UTL_SMTP.DATA (mail_conn, v_msg);


也可以把郵件前序和本文資訊分開來寫入到串連中去，但這樣做恐怕對於標題和本文的中文文題會顧此失彼了。

參考：1. 用utl_smtp發送郵件時的漢字解決方案

        2. 執行個體講解如何通過Oracle成功發送郵件

        3. 用oracle發送郵件（功能很全）
介紹了附件的發送
        4. Oracle UTL_SMTP

        5. 用telnet發郵件(支援smtp認證)

附註：在 Oracle(8.1.7及以上版本) 中可以用下面語句獲得字串的 base64 編碼，如：

select UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(UTL_RAW.cast_to_raw('user'))) from dual;
select UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(UTL_RAW.cast_to_raw('pass'))) from dual;