公司用oracle db, character set = ZHT16BIG5
我create左個store proc 用黎send email出街...
唯獨是個store proc一食簡體字之後...
亦試過將pi_message_body 轉做nvarchar2...


  utl_smtp.write_data( c, 'MIME-Version: 1.0' || utl_tcp.crlf );
  utl_smtp.write_data( c, 'Content-Type: text/plain; charset=utf-8' || utl_tcp.crlf );
  utl_smtp.write_data( c, 'Content-Transfer-Encoding: 8bit' || UTL_TCP.CRLF );
  /*Write message body*/   
  utl_smtp.write_raw_data(c, utl_raw.cast_to_raw(convert(utl_tcp.crlf || pi_message_body, 'utf8')));

你 database 係 ZHT16BIG5, 咁啲 簡體字 點儲㗎?


你 database 係 ZHT16BIG5, 咁啲 簡體字 點儲㗎?
henrywho 發表於 2011-5-31 19:21

不過唔係知係咪utl_smtp唔support nvarchar2...
send新d email都變番繁體字...


本帖最後由 henrywho 於 2011-6-1 14:36 編輯

What version of Oracle?

Oracle 10g or above (or perhaps since 9i) supports only UTF8 or AL16UTF16 for NCHAR.  So you are storing Simplified Chinese in Unicode.  When you use utl_smtp, the unicode NCHAR data will be converted to your database character set implicitly.  And Oracle seems to have a "feature" helping us to translate Simplified Chinese to Traditional Chinese when we convert from unicode character sets to Big5 character sets (if it doesn't, they will all become question marks).  Hence you find your message translated.

If your emails are in html format, you can use asciistr() to convert those Simplified Chinese unicode into '\HHHH' forms, then use regexp_replace() to further convert them into '&#xHHHH;'.  Then it won't get translated to Traditional Chinese characters when converted to ZHT16BIG5 explicitly.


本帖最後由 unknown2 於 2011-6-1 15:37 編輯

no easy way, i develop a complex plsql package for this

1. all file in utf-8 encoding, full unicode support, tested in Oracle 10g (native UTF8 charset)
2. do not expect that the script work immediately, you may need to fix compilation bug
3. for the "mail_pkg_body.sql" file, you need to edit line 215,216 to provide the smtp server ip address
4. use "testcase.sql" as a starting point
5. actually, this package support unicode file attachment, but i will not teach you, learn it yourself if you want


As I said, if his email client supports html, it is then not that complicated.