ORACLE 10gR2 mail範例說明

先以os的權限登入主機(範例為unix系統)
(請確認是否有執行以下package的權限)

login as:
xxxxxxxxxxxxx password:

[XXXX@XXXXXX ~]$ sqlplus / as sysdba

SQL> @$ORACLE_HOME/rdbms/admin/utlsmtp.sql
--請注意SQL> @中間有一個空格

SQL> @$ORACLE_HOME/rdbms/admin/prvtsmtp.plb
--以上兩個為10g以前的功能

SQL> @$ORACLE_HOME/rdbms/admin/utlmail.sql

SQL> @$ORACLE_HOME/rdbms/admin/prvtmail.plb
--以上兩個為10g提供的功能(感覺比較好用)

以下為是一個小job(我是在PL/SQL中用SQL WINDOW執行)

BEGIN
DECLARE
pSender VARCHAR2(40) := 'ITman';
pRecipient VARCHAR2(40);
pSubject VARCHAR2(40) := 'IT man Blog';
pMessage VARCHAR2(120) := 'Welcome ITman Blog http://toskyshow.blogspot.com/';

mailhost CONSTANT VARCHAR2(30) := 'xx.xx.xx.xx'; --mail server
--請注意你的DNS有沒有被解析 如果沒有就直接打IP address 不然會發不出去喔
--筆者就被這個煩了一下午
crlf CONSTANT VARCHAR2(2) := CHR(13) || CHR(10);
mesg VARCHAR2(1000);
mail_conn UTL_SMTP.CONNECTION;

BEGIN
BEGIN
pRecipient := 'xxx@xxx.xxx.xxx'; --請打入收件者的mail address
mail_conn := utl_smtp.open_connection(mailhost, 25);
mesg := 'From: <' || pSender || '>' || crlf || 'Date: ' ||
TO_CHAR(SYSDATE, 'DD/MM/YYYY HH24:MI:SS') || crlf ||
'Subject: ' || pSubject || crlf || 'To: ' || pRecipient || crlf || crlf ||
pMessage;
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('xxxx'))));
---以上xxx請打入帳號
utl_smtp.command(mail_conn,
utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw('xxxx'))));
---以上xxx請打入密碼
utl_smtp.mail(mail_conn, pSender);
utl_smtp.rcpt(mail_conn, pRecipient);

utl_smtp.data(mail_conn, mesg);

utl_smtp.quit(mail_conn);
dbms_output.put_line('set mail sucess account = ' || pRecipient);
END;

EXCEPTION
WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
BEGIN
UTL_SMTP.QUIT(mail_conn);
dbms_output.put_line('S ERROR=' || SQLERRM);
EXCEPTION
WHEN UTL_SMTP.TRANSIENT_ERROR OR UTL_SMTP.PERMANENT_ERROR THEN
dbms_output.put_line(SQLERRM);
NULL;
END;
raise_application_error(-20000,
'Failed to send mail due to : ' || SQLERRM);
END;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(SQLERRM);
NULL;
END;