分類: PL/SQL

create or replace
PROCEDURE sendmail(func IN VARCHAR2,fname IN VARCHAR2)
AS
v_From      VARCHAR2(80) := ‘admin@test.com.tw’;
v_list      VARCHAR2(255) := ‘a@test.com.tw,b@test.com.tw,c@test.com.tw,d@test.com.tw’;
v_Subject   VARCHAR2(80);
v_Mail_Host VARCHAR2(30) := ‘mailhost.test.com.tw’;
v_Mail_Conn utl_smtp.Connection;
v_user      VARCHAR2(20) := ‘admin’;
v_pass      VARCHAR2(20) := ‘admin_password’;
crlf        VARCHAR2(2)  := chr(13)||chr(10);
f           utl_file.file_type;
v_func      BOOLEAN;
v_msg       VARCHAR2(255);
l_tablen    BINARY_INTEGER;
l_tab       DBMS_UTILITY.uncl_array;
BEGIN
case func
WHEN ‘safty_inv’ THEN
v_func := safty_inv(fname);
v_Subject := ‘郵件通知’;
END case;
–呼叫function產生要傳送的文字/html檔
IF (v_func) THEN
–連接郵件主機傳送smtp驗證資訊
v_Mail_Conn := utl_smtp.Open_Connection(v_Mail_Host, 25);
UTL_SMTP.ehlo(v_Mail_Conn, v_Mail_Host);
utl_smtp.command(v_Mail_Conn, ‘AUTH LOGIN’);
UTL_SMTP.command(v_Mail_Conn,UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(UTL_RAW.cast_to_raw(v_user))));
UTL_SMTP.command(v_Mail_Conn,UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(UTL_RAW.cast_to_raw(v_pass))));
–寄件人
utl_smtp.Mail(v_Mail_Conn, v_From);
–寄送給複數收件人
DBMS_UTILITY.comma_to_table (
list   => v_list,
tablen => l_tablen,
tab    => l_tab);
FOR i IN 1 .. l_tablen LOOP
utl_smtp.Rcpt(v_Mail_Conn, l_tab(i));
END LOOP;
–組成郵件檔頭
utl_smtp.open_data(v_Mail_Conn);
–寄件者
UTL_SMTP.write_raw_data(v_mail_conn, utl_raw.cast_to_raw(‘From: ‘ || v_from || CRLF));
–收件者
DBMS_UTILITY.comma_to_table (
list   => v_list,
tablen => l_tablen,
tab    => l_tab);
FOR i IN 1 .. l_tablen LOOP
UTL_SMTP.write_raw_data(v_mail_conn, utl_raw.cast_to_raw(‘To: ‘   || l_tab(i) || CRLF));
END LOOP;
–UTF8主旨&指定郵件本文MIME格式/編碼方式
UTL_SMTP.write_raw_data(v_mail_conn, utl_raw.cast_to_raw(‘Subject: =?utf-8?B?’|| UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(UTL_RAW.cast_to_raw(v_Subject))) || ‘?=’ || CRLF));
utl_smtp.write_data(v_mail_conn,’MIME-Version: 1.0’|| CRLF);
utl_smtp.write_data(v_mail_conn,’Content-Type: multipart/mixed; boundary=”‘|| ‘boundary_str’ ||'”‘|| CRLF);
utl_smtp.write_data(v_mail_conn, CRLF);
–本文第一部份,郵件文字內容
utl_smtp.write_data(v_mail_conn,’–boundary_str’|| CRLF);
utl_smtp.write_data(v_mail_conn,’MIME-Version: 1.0’|| CRLF);
utl_smtp.write_data(v_mail_conn,’Content-Type: text/plain; charset=”utf-8″‘|| CRLF);
utl_smtp.write_data(v_mail_conn,’Content-Transfer-Encoding: 8bit’|| CRLF);
utl_smtp.write_data(v_mail_conn, ” || CRLF); –空行
utl_smtp.write_data(v_mail_conn, CRLF);
–第二部份:附件內容&指定MIME格式/編碼方式
utl_smtp.write_data(v_mail_conn,’–boundary_str’|| CRLF);
utl_smtp.write_data(v_mail_conn,’Content-Type: text/plain;’|| CRLF);
utl_smtp.write_data(v_mail_conn,’        name=”‘||fname||'”‘|| crlf);
utl_smtp.write_data(v_mail_conn,’Content-Transfer-Encoding: 8bit’|| CRLF);
utl_smtp.write_data(v_mail_conn,’Content-Disposition: attachment;’|| crlf);
utl_smtp.write_data(v_mail_conn,’        filename=”‘||fname||'”‘|| crlf);
utl_smtp.write_data(v_mail_conn, crlf);
–讀取來源文字類型附件&一行一行寫入
f := utl_file.fopen(‘REPOUT’,fname,’R’);
IF utl_file.is_open(f) THEN
LOOP
BEGIN
utl_file.get_line(f, v_msg);
UTL_SMTP.write_raw_data(v_mail_conn, utl_raw.cast_to_raw(v_msg || crlf));
–例外處理
EXCEPTION
WHEN NO_DATA_FOUND THEN
EXIT;
END;
END LOOP;
ELSE
utl_file.fclose(f);
END IF;
utl_smtp.write_data(v_mail_conn, crlf);
utl_smtp.write_data(v_mail_conn,’–boundary_str–‘|| CRLF);
UTL_SMTP.close_data(v_mail_conn);
–結束連線
utl_smtp.Quit(v_mail_conn);
END IF;
–寄送錯誤處理
EXCEPTION
WHEN utl_smtp.Transient_Error OR utl_smtp.Permanent_Error then
raise_application_error(-20000, ‘Unable to send mail: ‘||sqlerrm);
END;