utl_smtp發送Mail

 CREATE OR REPLACE PROCEDURE send_mail (

   p_to        IN VARCHAR2,

   p_cc        IN VARCHAR2,

                                       p_from      IN VARCHAR2,

                                       p_mail_subject IN VARCHAR2, 

                                       p_message   IN VARCHAR2,

                                       p_smtp_host IN VARCHAR2,

                                       p_smtp_port IN NUMBER DEFAULT 25,

                                       p_mime_type IN VARCHAR2 DEFAULT NULL,

                                       p_directory IN VARCHAR2 DEFAULT NULL,

                                       p_attach    IN VARCHAR2 DEFAULT NULL )

AS

/********************************

 * 使用範例:

 * BEGIN

 * apex_ws_erp.send_mail(p_to => 'kn@mail.com.tw',

 *           p_cc => kn@mail.com.tw;kn@mail.com.tw',

 *           p_from      => 'admin@mail.com.tw',

 *           p_mail_subject => 'Subject test', 

 *           p_message   => '<b>This is Test msessage</b><p>hello world</p><p>哈囉你好嗎</p>',

 *           p_smtp_host => 'localhost',

 *           p_mime_type => 'text/plain',

 *           p_directory => 'DUMP_CSV_DIR',

 *           p_attach => 'test.csv'); 

 * END; 

 * 

 * 

 */  

  l_mail_conn   utl_smtp.connection;

  CRLF          varchar2(2) := CHR( 13 ) || CHR( 10 );

  --utl_smtp.write_data 用來放置一般資訊

  --utl_smtp.write_raw_data 用來放置 16 進位資訊, 也是中文字必須轉換成 16 進位才可正常顯示

  file_handle     bfile;

  file_exists     boolean;

  block_size      number;

  file_len        number;

  pos             number;

  total           number;

  read_bytes      number;

  data            raw(200);

 

  loopcount       number;

  att_count       number;

BEGIN

  -- 開啟 Mail Connection 物件

  l_mail_conn := utl_smtp.open_connection(p_smtp_host, p_smtp_port);

  -- 建立連線

  utl_smtp.helo(l_mail_conn, p_smtp_host);

  -- 設定 寄件者

  utl_smtp.mail(l_mail_conn, p_from);

  -- 設定 收件者

  --utl_smtp.rcpt(l_mail_conn, p_to);

  -- 設定 多個收件者

  FOR r1 IN (SELECT NVL(REGEXP_SUBSTR(p_to||';'||p_cc, '[^;]+', 1, LEVEL, 'i'), 'NULL') AS mail_to 

FROM DUAL 

CONNECT BY LEVEL <= LENGTH(p_to||';'||p_cc) - LENGTH(REGEXP_REPLACE(p_to||';'||p_cc, ';', ''))+1)

  LOOP 

    IF r1.mail_to <> 'NULL' THEN 

  utl_smtp.rcpt(l_mail_conn, r1.mail_to);

END IF;

  END LOOP;

  -- 設定 發信內容

  utl_smtp.open_data(l_mail_conn);

  -- 寫入 Mail Header

  utl_smtp.write_raw_data(l_mail_conn, utl_raw.cast_to_raw('Subject:'||p_mail_subject|| CRLF));

  utl_smtp.write_raw_data(l_mail_conn, utl_raw.cast_to_raw('To:'||p_to|| CRLF));

  utl_smtp.write_raw_data(l_mail_conn, utl_raw.cast_to_raw('Cc:'||p_cc|| CRLF)); 

  utl_smtp.write_data(l_mail_conn, 'MIME-Version: 1.0' || CRLF );  

  utl_smtp.write_data(l_mail_conn, 'Content-Type: multipart/mixed; boundary="gcboundary0p"'|| CRLF|| CRLF);

  /*

  IF p_message IS NOT NULL THEN 

  utl_smtp.write_data(l_mail_conn, '--gcboundary0p'||CRLF );

    utl_smtp.write_data(l_mail_conn, 'Content-Type: text/html; charset=utf-8/big5' || CRLF );

    utl_smtp.write_data(l_mail_conn, 'Content-Transfer-Encoding: 8bit' || CRLF );

    utl_smtp.write_raw_data(l_mail_conn, utl_raw.cast_to_raw(p_message) );

    utl_smtp.write_data(l_mail_conn, '' || CRLF );

  END IF;

 */

  IF p_message IS NOT NULL THEN

    utl_smtp.write_data(l_mail_conn, '--gcboundary0p'||CRLF);

    utl_smtp.write_data(l_mail_conn, 'Content-Type: text/html; charset="utf-8"' || CRLF || CRLF);

    --utl_smtp.write_data(l_mail_conn, 'Content-Transfer-Encoding: 8bit' || CRLF );

    utl_smtp.write_raw_data(l_mail_conn, utl_raw.cast_to_raw(p_message) );

    utl_smtp.write_data(l_mail_conn, CRLF || CRLF);

  END IF;

 

  dbms_output.put_line('Starting attachment segment');

  dbms_output.put_line('Directory: '||p_directory);

  dbms_output.put_line('AttachList: '||p_attach); 

  -- MAIL附件

  BEGIN 

  IF p_attach IS NOT NULL THEN 

    

    loopcount := 0; 

    loopcount := loopcount +1;

  dbms_output.put_line('Attaching: '||p_directory||'/'||p_attach);

  utl_file.fgetattr(p_directory, p_attach, file_exists, file_len, block_size);

  IF file_exists THEN 

  dbms_output.put_line('Getting mime_type for the attachment');

      utl_smtp.write_data(l_mail_conn, '--gcboundary0p'||CRLF );

  utl_smtp.write_data(l_mail_conn, 'Content-Type: '||p_mime_type ||'; name="' || p_attach || '"' || CRLF );

  utl_smtp.write_data(l_mail_conn, 'Content-Transfer-Encoding: base64' || CRLF );

  utl_smtp.write_data(l_mail_conn, 'Content-Disposition: attachment; filename="'||p_attach||'"' || CRLF );

  utl_smtp.write_data(l_mail_conn,  CRLF );

 

  file_handle := bfilename(p_directory, p_attach);

  pos := 1;

  total := 0;

  file_len := dbms_lob.getlength(file_handle);

  dbms_lob.open(file_handle, dbms_lob.lob_readonly);

LOOP 

IF pos + 57 - 1 > file_len THEN 

              read_bytes := file_len - pos + 1;

              --dbms_output.put_line('Last read - Start: '||pos);

            ELSE 

              --dbms_output.put_line('Reading - Start: '||pos);

              read_bytes := 57;

            END IF;

            total := total + read_bytes;

            dbms_lob.read(file_handle, read_bytes, pos, data);

            utl_smtp.write_raw_data(l_mail_conn, utl_encode.base64_encode(data));

            

            pos := pos + 57;

            IF pos > file_len THEN

              EXIT;

            END IF;

END LOOP;

dbms_output.put_line('Length was '||file_len);

dbms_lob.close(file_handle);

        IF (loopcount < att_count) then

            utl_smtp.write_data(l_mail_conn,  CRLF );

            utl_smtp.write_data(l_mail_conn, '--gcboundary0p'||CRLF );

        ELSE 

        utl_smtp.write_data(l_mail_conn,  CRLF );

            utl_smtp.write_data(l_mail_conn, '--gcboundary0p--'||CRLF );

            dbms_output.put_line('Writing end boundary');

        END IF;

  ELSE 

          dbms_output.put_line('Skipping: '||p_directory||'/'||p_attach||'Does not exist.');

  END IF;  

  

  END IF;

 

  EXCEPTION WHEN OTHERS THEN 

  dbms_output.put_line('Error Message：'||sqlerrm);

  END;

  -- 結束連線

  utl_smtp.close_data(l_mail_conn);

  utl_smtp.quit(l_mail_conn);

END;