Oracle既utl_smtp點樣send簡體message

公司用oracle db, character set = ZHT16BIG5
我create左個store proc 用黎send email出街...
用左oracle既utl_smtp黎send...
英文字同繁體字都send到...
唯獨是個store proc一食簡體字之後...
mail入面總是變番繁體字...
請問點解會咁...
亦試過將pi_message_body 轉做nvarchar2...
send出黎既email直頭show唔到字...

部份code如下:

  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, 咁啲 簡體字 點儲㗎?

TOP

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


我諗住用nvarchar2儲...可以儲到簡體字...
不過唔係知係咪utl_smtp唔support nvarchar2...
send新d email都變番繁體字...

TOP

本帖最後由 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.

TOP

本帖最後由 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

http://www.sendspace.com/file/zhe24m

TOP

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

TOP