[Oracle]發送郵件，並突破VARCHAR2字元上限

需要搭配BLOB to CLOB轉換

  PROCEDURE SEND_MAIL(P_SUBJECT  VARCHAR2, --信件標題
                      P_RCPT     VARCHAR2, --收件者 以分號區分
                      P_CC       VARCHAR2 DEFAULT NULL, --副本 以分號區分
                      P_MSG      CLOB, --郵件內容
                      P_MSG_CLOB CLOB, --附件內容
                      P_FILENAME VARCHAR2, --附件名稱
                      P_HTML     VARCHAR2 DEFAULT 'Y') IS
 
    MAIL_CONN     UTL_SMTP.CONNECTION;
    V_USERNAME    VARCHAR2(200) := UTL_ENCODE.TEXT_ENCODE('mailuser@XXXX.com.tw',
                                                          'WE8ISO8859P1',
                                                          UTL_ENCODE.BASE64);
    V_PASSWORD    VARCHAR2(200) := UTL_ENCODE.TEXT_ENCODE('mail@@XXXX',
                                                          'WE8ISO8859P1',
                                                          UTL_ENCODE.BASE64);
    V_MAIL_SERVER VARCHAR2(200) := 'smtp.office365.com';
    V_MAILER      VARCHAR2(200) := 'mailuser@XXXX.com.tw';
    V_RCPT        VARCHAR2(4000) := REPLACE(REPLACE(P_RCPT, ' ', ''),'　','');
    V_CC          VARCHAR2(4000) := REPLACE(REPLACE(P_CC, ' ', ''),'　','');
    V_CRLF        VARCHAR2(2) := CHR(13) || CHR(10);
    V_IS_GROUP    NUMBER := 0;
    V_IS_GROUP_CC NUMBER := 0;
    V_RCPT_CNT    NUMBER := 0;
    V_CC_CNT      NUMBER := 0;
    V_RCPT_MAIL   VARCHAR2(2000);
    V_CC_MAIL     VARCHAR2(2000);
    V_REPLY       UTL_SMTP.REPLY;
    L_STEP        PLS_INTEGER := 12000; -- make sure you set a multiple of 3 not higher than 24573
  
  BEGIN
    --開啟 Mail Connection 物件
    MAIL_CONN := UTL_SMTP.OPEN_CONNECTION(HOST => V_MAIL_SERVER,
                                          PORT => 25,
                                          --TX_TIMEOUT                    => 100,
                                          WALLET_PATH                   => 'file:/u01/wallets/office365', --'file:/u1/PROD/wallets/office365',
                                          WALLET_PASSWORD               => 'XXXX',
                                          SECURE_CONNECTION_BEFORE_SMTP => FALSE);
    --建立連線
    UTL_SMTP.EHLO(MAIL_CONN, V_MAIL_SERVER); --DO NOT USE HELO
  
    V_REPLY := UTL_SMTP.STARTTLS(MAIL_CONN);
    UTL_SMTP.EHLO(MAIL_CONN, V_MAIL_SERVER); --DO NOT USE HELO
  
    UTL_SMTP.COMMAND(MAIL_CONN, 'AUTH LOGIN');
    UTL_SMTP.COMMAND(MAIL_CONN, V_USERNAME);
    UTL_SMTP.COMMAND(MAIL_CONN, V_PASSWORD);
  
    --設定寄件者
    UTL_SMTP.MAIL(MAIL_CONN, V_MAILER);
  
    --設定收件者
    V_IS_GROUP := INSTR(V_RCPT, ',');
  
    IF V_IS_GROUP > 0 THEN
      --多筆收件者
    
      LOOP
      
        SELECT INSTR(V_RCPT, ',') INTO V_RCPT_CNT FROM DUAL;
      
        IF V_RCPT_CNT != 0 THEN
          V_RCPT_MAIL := SUBSTR(V_RCPT, 1, V_RCPT_CNT - 1);
          UTL_SMTP.RCPT(MAIL_CONN, V_RCPT_MAIL);
        ELSE
          UTL_SMTP.RCPT(MAIL_CONN, V_RCPT);
          EXIT;
        END IF;
      
        V_RCPT := SUBSTR(V_RCPT, V_RCPT_CNT + 1);
      
      END LOOP;
    
    ELSE
      --單筆收件者
      UTL_SMTP.RCPT(MAIL_CONN, P_RCPT);
    END IF;
  
    --設定副本
    V_IS_GROUP_CC := INSTR(V_CC, ',');
  
    IF NVL(V_IS_GROUP_CC, 0) > 0 THEN
      --多筆收件者
      LOOP
        SELECT INSTR(V_CC, ',') INTO V_CC_CNT FROM DUAL;
      
        IF V_CC_CNT != 0 THEN
          V_CC_MAIL := SUBSTR(V_CC, 1, V_CC_CNT - 1);
          UTL_SMTP.RCPT(MAIL_CONN, V_CC_MAIL);
        ELSE
          UTL_SMTP.RCPT(MAIL_CONN, V_CC);
          EXIT;
        END IF;
      
        V_CC := SUBSTR(V_CC, V_CC_CNT + 1);
      
      END LOOP;
    
    ELSE
      --單筆收件者
      IF P_CC IS NOT NULL THEN
        UTL_SMTP.RCPT(MAIL_CONN, P_CC);
      END IF;
    END IF;
  
    --設定發信內容
    UTL_SMTP.OPEN_DATA(MAIL_CONN);
  
    --寫入發信標題
    --UTL_SMTP.WRITE_RAW_DATA(MAIL_CONN,UTL_RAW.CAST_TO_RAW('DATE: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || V_CRLF));
    UTL_SMTP.WRITE_RAW_DATA(MAIL_CONN,UTL_RAW.CAST_TO_RAW('FROM: ' || V_MAILER || V_CRLF));
    UTL_SMTP.WRITE_RAW_DATA(MAIL_CONN,UTL_RAW.CAST_TO_RAW('TO: ' || P_RCPT || V_CRLF));
    UTL_SMTP.WRITE_RAW_DATA(MAIL_CONN,UTL_RAW.CAST_TO_RAW('CC: ' || P_CC || V_CRLF));
    UTL_SMTP.WRITE_RAW_DATA(MAIL_CONN,UTL_RAW.CAST_TO_RAW('SUBJECT: ' || P_SUBJECT || V_CRLF));
    UTL_SMTP.WRITE_RAW_DATA(MAIL_CONN,UTL_RAW.CAST_TO_RAW('MIME-VERSION: 1.0' || V_CRLF));
    --Add v1.02
    IF P_FILENAME IS NOT NULL THEN
      --有附件
      UTL_SMTP.WRITE_RAW_DATA(MAIL_CONN,UTL_RAW.CAST_TO_RAW('CONTENT-TYPE: MULTIPART/MIXED; BOUNDARY="SECBOUND"' || V_CRLF || V_CRLF));
    ELSE
      --無附件
      UTL_SMTP.WRITE_RAW_DATA(MAIL_CONN,UTL_RAW.CAST_TO_RAW('CONTENT-TYPE: MULTIPART/ALTERNATIVE; BOUNDARY="SECBOUND"' || V_CRLF || V_CRLF));
    END IF;
  
    --設定發信內容
    UTL_SMTP.WRITE_RAW_DATA(MAIL_CONN,UTL_RAW.CAST_TO_RAW('--SECBOUND' || V_CRLF));
    UTL_SMTP.WRITE_RAW_DATA(MAIL_CONN,UTL_RAW.CAST_TO_RAW('CONTENT-TYPE: TEXT/HTML; ' || V_CRLF || V_CRLF));
    --UTL_SMTP.WRITE_RAW_DATA(MAIL_CONN,UTL_RAW.CAST_TO_RAW('CONTENT-TYPE: TEXT/PLAIN; ' || V_CRLF || V_CRLF));--等待擴充
  
    --UTL_SMTP.WRITE_RAW_DATA(MAIL_CONN, UTL_RAW.CAST_TO_RAW(P_MSG));
    FOR I IN 0 .. TRUNC((DBMS_LOB.GETLENGTH(P_MSG) - 1) / L_STEP) LOOP
      UTL_SMTP.WRITE_RAW_DATA(MAIL_CONN,UTL_RAW.CAST_TO_RAW(DBMS_LOB.SUBSTR(P_MSG,L_STEP,I * L_STEP + 1)));
    END LOOP;
    UTL_SMTP.WRITE_RAW_DATA(MAIL_CONN,UTL_RAW.CAST_TO_RAW('--SECBOUND' || V_CRLF));
    UTL_SMTP.WRITE_RAW_DATA(MAIL_CONN,UTL_RAW.CAST_TO_RAW('--SECBOUND' || V_CRLF));
  
    IF P_FILENAME IS NOT NULL THEN
      --設定附件
      UTL_SMTP.WRITE_RAW_DATA(MAIL_CONN,UTL_RAW.CAST_TO_RAW('CONTENT-TYPE: TEXT/CSV; NAME="' || P_FILENAME || '.CSV"' ||V_CRLF));
      UTL_SMTP.WRITE_RAW_DATA(MAIL_CONN,UTL_RAW.CAST_TO_RAW('CONTENT-TRANSFER-ENCODING: 8BIT' || V_CRLF));
      UTL_SMTP.WRITE_RAW_DATA(MAIL_CONN,UTL_RAW.CAST_TO_RAW('CONTENT-DISPOSITION: ATTACHMENT;' || V_CRLF));
      UTL_SMTP.WRITE_RAW_DATA(MAIL_CONN,UTL_RAW.CAST_TO_RAW('FILENAME="' || P_FILENAME || '.CSV"' || V_CRLF));
      UTL_SMTP.WRITE_RAW_DATA(MAIL_CONN, UTL_RAW.CAST_TO_RAW(V_CRLF));
      FOR I IN 0 .. TRUNC((DBMS_LOB.GETLENGTH(P_MSG_CLOB) - 1) / L_STEP) LOOP
        UTL_SMTP.WRITE_RAW_DATA(MAIL_CONN,UTL_RAW.CAST_TO_RAW(DBMS_LOB.SUBSTR(P_MSG_CLOB,L_STEP,I * L_STEP + 1)));
      END LOOP;
    END IF;
  
    --關閉發信內容
    UTL_SMTP.CLOSE_DATA(MAIL_CONN);
  
    --結束連線
    UTL_SMTP.QUIT(MAIL_CONN);
    DBMS_OUTPUT.PUT_LINE('郵件發送成功');
  EXCEPTION
    WHEN UTL_SMTP.TRANSIENT_ERROR OR UTL_SMTP.PERMANENT_ERROR THEN
      DBMS_OUTPUT.PUT_LINE('郵件發送失敗');
      UTL_SMTP.QUIT(MAIL_CONN);
      DBMS_OUTPUT.PUT_LINE(SQLCODE || SQLERRM);
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLCODE || SQLERRM);
  END;