Oracle 發送HTML格式mail中文亂碼的解決

最近研究用oracle定義job并用來發送mail且mail的格式是HTML的,做的過程中從網上找了些代碼,但是出現中文亂碼的問題,做了一下修改。代碼如下： procedure send_mail IS l_boundary varchar2(255) default 'a1b2c3d4e3f2g1'; l_connection utl_smtp.connection; l_body_html clob := empty_clob; --This LOB will be the email message l_offset number; l_ammount number; l_temp varchar2(32767) default null; p_to varchar2(200);--收件人 p_html varchar2(32767);--內容 p_subject varchar2(1000);--標題 p_smtp_hostname VARCHAR2(30) := ''; --SMTP服務器地址 v_user VARCHAR2(30) :='' ; --登錄SMTP服務器的用戶名 v_pass VARCHAR2(20) := ''; --登錄SMTP服務器的密碼 p_from VARCHAR2(50) := ''; --發送者郵箱 begin p_subject:=''; p_html:=''; p_to:=''; ------------------------下面的代碼不要變------------------------------------ l_connection := utl_smtp.open_connection(p_smtp_hostname,25); utl_smtp.ehlo( l_connection, p_smtp_hostname ); --加上驗證 /* UTL_SMTP.command(l_connection, 'AUTH LOGIN'); -- smtp服務器登入 UTL_SMTP.command(l_connection,UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(UTL_RAW.cast_to_raw(v_user)))); UTL_SMTP.command(l_connection,UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(UTL_RAW.cast_to_raw(v_pass)))); */ utl_smtp.mail( l_connection, p_from ); utl_smtp.rcpt( l_connection, p_to ); l_temp := l_temp || 'MIME-Version: 1.0' || chr(13) || chr(10); l_temp := l_temp || 'To: ' || p_to || chr(13) || chr(10); l_temp := l_temp || 'From: ' || p_from || chr(13) || chr(10); l_temp := l_temp || 'Subject: ' || p_subject || chr(13) || chr(10); l_temp := l_temp || 'Content-Type: multipart/alternative;Charset=big5; boundary=' || chr(34) || l_boundary || chr(34) || chr(13) || chr(10); dbms_lob.createtemporary(l_body_html, false, 10 ); dbms_lob.write(l_body_html,length(l_temp),1,l_temp); l_offset := dbms_lob.getlength(l_body_html) + 1; l_temp := chr(13)||chr(10)||'--' || l_boundary || chr(13)||chr(10); l_temp := l_temp || 'content-type: text/plain; charset=big5' || chr(13) || chr(10) || chr(13) || chr(10); dbms_lob.write(l_body_html,length(l_temp),l_offset,l_temp); l_temp := chr(13)||chr(10)||chr(13)||chr(10)||'--' || l_boundary || chr(13) || chr(10); l_temp := l_temp || 'content-type: text/html;charset=big5' || chr(13) || chr(10) || chr(13) || chr(10); l_offset := dbms_lob.getlength(l_body_html) + 1; dbms_lob.write(l_body_html,length(l_temp),l_offset,l_temp); l_offset := dbms_lob.getlength(l_body_html) + 1; dbms_lob.write(l_body_html,length(p_html),l_offset,p_html); l_temp := chr(13) || chr(10) || '--' || l_boundary || '--' || chr(13); l_offset := dbms_lob.getlength(l_body_html) + 1; dbms_lob.write(l_body_html,length(l_temp),l_offset,l_temp); l_offset := 1; l_ammount := 1900; utl_smtp.open_data(l_connection); while l_offset < dbms_lob.getlength(l_body_html) loop --我這個處理的是中文繁體------------------- utl_smtp.write_raw_data(l_connection,utl_raw.cast_to_raw(convert(dbms_lob.substr(l_body_html,l_ammount,l_offset),'ZHT16MSWIN950'))); ----------------------------------------------- l_offset := l_offset + l_ammount ; l_ammount := least(1900,dbms_lob.getlength(l_body_html) - l_ammount); end loop; utl_smtp.close_data(l_connection); utl_smtp.quit( l_connection ); dbms_lob.freetemporary(l_body_html); end;