UTL_SMTP發送郵件,防中文亂碼處理！

DECLARE
  V_CONN                UTL_SMTP.CONNECTION;
  L_REC_ARR             ARRAY;
  V_DB_NLS_CHARACTERSET VARCHAR2(40);
  CURSOR L_TEMP IS
    SELECT COL1, COL2, COL3, COL4, COL5, COL6, COL7 FROM TABLE_NAME;
  ;

  L_REC_LIST    VARCHAR2(32767);
  BODY_HTML_S   VARCHAR2(32767);
  BODY_HTML_E   VARCHAR2(20);
  BODY_HTML_C   VARCHAR2(32767);
  L_MEG_CONTENT VARCHAR2(32767);
BEGIN
  --?峰?????″?ㄥ??绗???
  SELECT VALUE
    INTO V_DB_NLS_CHARACTERSET
    FROM NLS_DATABASE_PARAMETERS
   WHERE PARAMETER = 'NLS_CHARACTERSET';

  --?峰????浠舵?ユ?朵漢??琛???煎?濡?([email?protected],[email?protected],[email?protected])
  SELECT ADL.TO_RECIPIENTS
    INTO L_REC_LIST
    FROM ALR_DISTRIBUTION_LISTS ADL
   WHERE ADL.NAME = 'ALERT_REC_LIST';

  --琛ㄥごHTML浠ｇ??
  BODY_HTML_S := '<table width="100%"   cellspacing="1" cellpadding="0" bgcolor="#000000" style="font-size:12px; font-family:Arial, Helvetica, sans-serif">' ||
                 '<tr>' ||
                 '<th bgcolor="#0099ff" scope="col">Header 1</th>' ||
                 '<th bgcolor="#0099ff" scope="col">Header 2</th>' ||
                 '<th bgcolor="#0099ff" scope="col">Header 3</th>' ||
                 '<th bgcolor="#0099ff" scope="col">Header 4</th>' ||
                 '<th bgcolor="#0099ff" scope="col">Header 5</th>' ||
                 '<th bgcolor="#0099ff" scope="col">Header 6</th>' ||
                 '<th bgcolor="#0099ff" scope="col">Header 7</th>' ||
                 '</tr>';

  --寰????版??琛?
  FOR R IN L_TEMP LOOP
    BODY_HTML_C := BODY_HTML_C || '<tr>' ||
                   '<td width="8%" bgcolor="#FFFFFF" align="left">' ||
                   NVL(R.COL1, '?') || '</td>' || UTL_TCP.CRLF ||
                   '<td width="12%" bgcolor="#FFFFFF" align="left">' ||
                   NVL(R.COL2, '?') || '</td>' || UTL_TCP.CRLF ||
                   '<td width="20%" bgcolor="#FFFFFF" align="left">' ||
                   NVL(R.COL3, '?') || '</td>' || UTL_TCP.CRLF ||
                   '<td width="10%" bgcolor="#FFFFFF" align="left">' ||
                   NVL(R.COL4, '?') || '</td>' || UTL_TCP.CRLF ||
                   '<td width="8%" bgcolor="#FFFFFF" align="center">' ||
                   NVL(R.COL5, '?') || '</td>' || UTL_TCP.CRLF ||
                   '<td width="8%" bgcolor="#FFFFFF" align="center">' ||
                   NVL(R.COL6, '?') || '</td>' || UTL_TCP.CRLF ||
                   '<td width="8%" bgcolor="#FFFFFF" align="center">' ||
                   NVL(R.COL7, '?') || '</td>' || UTL_TCP.CRLF ||
                   '</tr>';
  END LOOP;
  --琛ㄦ?肩???浠ｇ??
  BODY_HTML_E := '</table>';
  --杩??ラ??浠舵???″??  V_CONNECTION := UTL_SMTP.OPEN_CONNECTION('10.2.3.111', 25);
  UTL_SMTP.HELO(V_CONN, '10.2.3.111');
  UTL_SMTP.COMMAND(V_CONN, 'AUTH LOGIN');
  --韬?浠介??璇?锛?濡??????″?ㄥ??璁稿?垮??锛?姝ら?ㄥ????浠ユ敞????
  UTL_SMTP.COMMAND(V_CONN,
                   UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(UTL_RAW.CAST_TO_RAW('[email?protected]'))));
  UTL_SMTP.COMMAND(V_CONN,
                   UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(UTL_RAW.CAST_TO_RAW('*****'))));
  --??浠朵漢
  UTL_SMTP.MAIL(V_CONN, '"Workflow Mailer"<[email?protected]>');
  --??","瀛?绗??????朵歡浜哄??绗?覆锛?骞惰???浼??扮?.
  L_REC_ARR := SPLIT(L_REC_LIST, ',');
  --?朵歡浜猴?澶?涓??ユ?朵漢??浠ュ驚??璇ヤ唬??锛?
  FOR I IN 1 .. L_REC_ARR.COUNT LOOP
    UTL_SMTP.RCPT(V_CONN, L_REC_ARR(I) || '<' || L_REC_ARR(I) || '>');
  END LOOP;

  UTL_SMTP.OPEN_DATA(V_CONN);
  --??浠朵漢
  UTL_SMTP.WRITE_DATA(V_CONN,
                      'From: ' || '[email?protected]' || UTL_TCP.CRLF);

  L_MEG_CONTENT := L_MEG_CONTENT || 'MIME-Version: 1.0' || UTL_TCP.CRLF;
  --?朵歡浜猴?澶?涓??ユ?朵漢??浠ュ驚??璇ヤ唬??锛?
  FOR I IN 1 .. L_REC_ARR.COUNT LOOP
    L_MEG_CONTENT := L_MEG_CONTENT || 'To: ' || L_REC_ARR(I) || '<' ||
                     L_REC_ARR(I) || '>' || UTL_TCP.CRLF;
  END LOOP;

  --寮?濮??兼?ラ??浠舵?煎?瀛?绗?覆
  L_MEG_CONTENT := L_MEG_CONTENT || 'Subject: ' || '杩???????浠朵富棰? ' ||
                   UTL_TCP.CRLF;
  L_MEG_CONTENT := L_MEG_CONTENT ||
                   'Content-Type: text/html;charset=gb2312' || UTL_TCP.CRLF;
  L_MEG_CONTENT := L_MEG_CONTENT || 'Content-Transfer-Encoding: 8bit' ||
                   UTL_TCP.CRLF;
  --??浠惰??猴??蹭貢??澶???锛?
  UTL_SMTP.WRITE_RAW_DATA(V_CONN,
                          UTL_RAW.CAST_TO_RAW(CONVERT(L_MEG_CONTENT ||
                                                      BODY_HTML_S ||
                                                      BODY_HTML_C ||
                                                      BODY_HTML_E
                                                      UTL_TCP.CRLF,
                                                      'ZHS16GBK',
                                                      V_DB_NLS_CHARACTERSET)));
  UTL_SMTP.CLOSE_DATA(V_CONNECTION);
  UTL_SMTP.QUIT(V_CONNECTION);
END;