從Oracle9i中傳送電子郵件

Oracle的包utl_smtp提供了從資料庫中直接傳送電子郵件的功能，這裡介紹一下其使用方法

網上找到的例子以及Oracle文件中的例子都沒有提到authorized的問題，我的測試中使用的是
163的免費信箱，傳送時老是報錯：
ORA-20000: Failed to send mail due to the following error:
ORA-29279: SMTP 永久性錯誤: 553 You are not authorized to send mail as , authentication is required

後來在metalink上找到解決方法：要在程式中增加登入smtp伺服器的過程！

測試環境： RedHat AS 3.0 Oracle 9.2.0.6
SMTP伺服器： smtp.163.com(ip地址202.108.44.205）

下面是儲存過程的原始碼，注意資料庫伺服器必須要能訪問smtp.163.com

CREATE OR REPLACE PROCEDURE send_mail (
p_recipient IN VARCHAR2,
p_message IN VARCHAR2,
p_subject IN VARCHAR2 default 'Oracle Perf Report '||to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),
p_sender IN VARCHAR2 default )

IS
v_mailhost varchar2(30) := '202.108.44.205'; -- SMTP伺服器地址
mail_conn utl_smtp.connection;
msg varchar2(4000);

p_user varchar2(30) := 'demo'; -- 登入SMTP伺服器的使用者名稱
p_pass varchar2(30) := 'demo'; -- 登入SMTP伺服器的密碼

BEGIN
/* 建立要傳送的郵件內容 注意報頭資訊和郵件正文之間要空一行 */
msg := 'Date:' ||to_char(sysdate,'dd mon yy hh24:mi:ss') || utl_tcp.CRLF ||
'From: ' || p_sender || '' ||utl_tcp.CRLF ||
'To: '|| p_recipient || '' ||utl_tcp.CRLF||
'Subject: '||p_subject || utl_tcp.CRLF ||utl_tcp.CRLF||
p_message;

-- dbms_output.put_line(msg);

mail_conn := utl_smtp.open_connection(v_mailhost,25);
utl_smtp.helo(mail_conn,v_mailhost);

/* smtp伺服器登入校驗 */
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);
utl_smtp.rcpt(mail_conn,p_recipient);

/* 傳送資料 */
utl_smtp.data(mail_conn, msg);
utl_smtp.quit(mail_conn);

EXCEPTION

WHEN OTHERS THEN
dbms_output.put_line(dbms_utility.format_error_stack);
dbms_output.put_line(dbms_utility.format_call_stack);

END send_mail;

測試這個儲存過程：
SQL> exec send_mail( is a oracle send mail demo!!!');

PL/SQL procedure successfully completed

參考資料：

1、《Oracle9i Supplied PL/SQL Packages and Types Reference》100 UTL_SMTP
2、網友mugen的《oracle 發郵件的程式》：http://blog.itpub.net/post/76/9913