ORACLE PL/SQL 透過 smtp auth 發送有附件的 mail

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;

在〈ORACLE PL/SQL 透過 smtp auth 發送有附件的 mail〉中有 26 則留言

  1. case func
    WHEN ‘safty_inv’ THEN
    v_func := safty_inv(fname);
    v_Subject := ‘郵件通知’;
    END case;

    請教 case會出現錯誤
    ‘safty_inv’的值應為多少?或一個function

    謝謝

  4. 類似像以下這樣吧 以下的plsql會產生一個叫做safty_inv的FUNCTION用來將查詢結果輸出簡單html, 你可以用Oracle sql developer這類輔助工具去建立 ….

  5. James

    謝謝您的回覆!
    function safty_inv我已建好

    傳進去有2個參數
    func應該給什麼值?
    fname 應該是檔案名稱，我預設為inv，需要給副檔名嗎?如inv.html

    謝謝

  6. 當初CASE是要寫來對應呼叫不同function的. func對應的就是你自己要呼叫的function名稱, 你的話就是safty_inv. 至於附檔名要給 htm/html 都行, 收件人郵件軟體認得就行.

  7. James

    謝謝您耐心回覆!
    我call這個SP 用SAFTY_INV,INV_HTML
    出現以下的錯誤
    ORA-29280: 目錄路徑無效
    ORA-06512: 在 “SYS.UTL_FILE”, line 41
    ORA-06512: 在 “SYS.UTL_FILE”, line 478
    ORA-06512: 在 “WMSTEST.SAFTY_INV”, line 11
    ORA-06512: 在 “WMSTEST.MAILTEST1_SP”, line 24
    ORA-06512: 在 line 8

    在Oracle Server是否要指定一個資料夾?

    謝謝!

  9. James

    SAFTY_INV裡的這一段是
    fname := utl_file.fopen(‘REPOUT’,output_file_name,’W’);
    是用 REPOUT
    CREATE or REPLACE DIRECTORY REPOUT as ‘+DATAFILES/’;
    GRANT READ, WRITE ON DIRECTORY REPOUT TO xxx;

    是否就可以排除目錄的問題?

    謝謝!

  11. James

    REPOUT目錄設定如下

    必須先把目錄建好，如D:\TEMP
    然後把REPOUT指到D:\TEMP
    CREATE or REPLACE DIRECTORY REPOUT as ‘D:\TEMP’
    設定REPOUT目錄存取權限給執行SP的使用者
    GRANT read, write on directory REPOUT to XXX

    可以寄信了
    但還碰到2個小問題
    1.產生HTML檔是亂碼
    2.寄出的信沒有附檔，如下
    ?boundary_str
    MIME-Version: 1.0
    Content-Type: text/plain; charset=utf-8
    Content-Transfer-Encoding: 8bit

    ?boundary_str
    Content-Type: text/plain;
    name=INV.HTML
    Content-Transfer-Encoding: 8bit
    Content-Disposition: attachment;
    filename=INV.HTML

    ?boundary_str?

    utl_smtp.write_data(v_mail_conn,’Content-Type: multipart/mixed; boundary= ‘ || ‘boundary_str’ || ” || CRLF);
    我這行格式好像有點問題

    謝謝

  12. James

    –第二部份:附件內容&指定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);

    由於復制時逗號會被轉換掉
    我的filename格式好像也有點問題

    SP是否有純文字檔，可以提供比對。
    感恩!

  14. James，早安：

    謝謝您的回覆!
    檔案產生OK，改為UTF-8檢視即正常。
    寄信OK，也有附檔INV.HTML，但不知為何附檔卻是空的沒有資料。

    謝謝您

  16. James

    謝謝您的回覆
    不是Function有問題就是執行者沒有寫入資料夾權限

    function有產生檔案，且是OK的
    –讀取來源文字類型附件&一行一行寫入
    f := utl_file.fopen(‘REPOUT’,fname,’R’);
    這段是把function產生的檔案給讀出來

    或資料夾是指信件的附檔嗎?

    謝謝!

  18. James

    我用dbms_output.put_line是能把資料讀出來，但不知為什麼寫不進去郵件的附檔。

    DECLARE
    f UTL_FILE.FILE_TYPE;
    v_msg VARCHAR2(255) ;
    BEGIN
    dbms_output.enable();
    f := utl_file.fopen(‘REPOUT’, ‘HAVI_INV.txt’, ‘R’);
    IF utl_file.is_open(f) THEN
    LOOP
    BEGIN
    utl_file.get_line(f, v_msg);
    dbms_output.put_line(v_msg);

    –例外處理
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    EXIT;
    END;
    END LOOP;
    ELSE
    utl_file.fclose(f);
    END IF;
    END;

    謝謝!

  19. 這就怪了…. 我把上面CODE剪下更改PROCEDURE名稱, EMAIL及帳密重新編譯產生是可以正常執行
    另外 get_line才是讀, put_line是寫檔或輸出到畫面
    你function不是可正確執行產生檔案? 那裏面就會用到put_line
    寫進郵件BODY是用UTL_SMTP.write_raw_data 也不是用put_line

  20. James

    謝謝您的回覆
    那一段PL SQL
    只是去測試從Directory(REPOUT)是否能把一個檔案(D:\TEMP\xxx.txt)裡的資料讀出來
    利用
    utl_file.get_line(f, v_msg);
    dbms_output.put_line(v_msg);
    只是驗證是否撈得到資料

    測試的結果是fuction可以順利產生檔案
    也可以從檔案把資料撈出來
    也可以寄送郵件

    但附件裡就是沒資料，目前就卡在這裡!

    謝謝您

  21. 你乾脆把sql查詢語法寫到cursor也不用寫到外部file 直接將查詢結果寫進附加檔 就不用去呼叫function

  23. James

    直接用cursor測試的結果，附加檔運作正常。

    資料夾權限的運作應該有問題
    建立資料夾D:\TEMP
    CREATE or REPLACE DIRECTORY REPOUT as ‘D:\TEMP’
    GRANT READ, WRITE ON DIRECTORY REPOUT to USER
    DIRECTORY 這樣授權可能有問題!

    謝謝您

  24. 程式只在AIX跟LINUX上跑過
    Windows上的話不清楚
    不過你是指
    GRANT READ, WRITE ON DIRECTORY REPOUT to USER
    DIRECTORY
    or
    GRANT READ, WRITE ON DIRECTORY REPOUT to USER
    ?? 這兩個就差很多 …

  25. James

    謝謝您的回覆
    我的環境Base on windows
    GRANT READ, WRITE ON DIRECTORY REPOUT to USER
    REPOUT是DIRECTORY NAME
    USER是Login User，如aaa

    SYS授權這個DIRECTORY給某個USER(aaa)使用

    以aaa登入PL SQL時可以看到REPOUT的目錄，選取REPOUT的使用者/角色(aaa)也顯示有READ WRITE的權限。

    謝謝您

  26. James

    在function裡的loop
    return true;
    utl_file.fclose(fname);
    對調成
    utl_file.fclose(fname);
    return true;

    測試的結果，就可撈到資料

    謝謝您，耐心的協助與敎導
    接下一個作業將檔案上傳 FTP
    感恩

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。