KB-Oracle 9i NVarchar求生守則

同事反應，一個CharacterSet設為ZHT16BIG5的Oracle 9i的資料庫，其中某個Table的NVarchar2欄位完全不接受非BIG5字元， 存入時會變成"?"。攪和了近一個月，反覆嘗試了多種工具、方法，也請教了朋友、求助Oracle Support、查了Oracle KB， 結論幾乎都指向---"Oracle 9i的NVarchar在非UTF8字元集資料庫裡無法接受非BIG5字元，而會將其轉為問號。 要解決這個問題的唯一方法是是將資料庫字元集設為UTF8，或將Client & Server都昇級至10g R2"。 (注意: 後來驗證的結果，這個結論不完全正確，但至少對N'...'表示法來說是如此)

直到今天，有機會重試一份簡單的Sample Code，碰巧又用對了檢查工具，才總算理出些頭緒。先來說說為什麼情況為什麼變得這麼複雜?

1.測量之前，先確定你的尺是直的!

這大概是這次摔最慘的地方: 你所使用的資料庫查詢工具可以正確地顯示非BIG5字元嗎? 用慣MS軟體與工具的人大概自WinNT時代起就很少擔心Unicode相容性問題，但這回常用的Oracle的搭配工具幾乎全軍覆沒! Oracle自家的SqlPlus都到9i版本了還不支援，著名的Toad 看來也不行! 倒是發現純Java開發的Aqua Data Studio 只要UI設定支援Unicode的TTF字型，顯示OK； 另一個用.NET寫的QueryExpress若選用Oracle Driver(意味要用Microsoft OLE DB Provider For Oracle而不是Oracle Provider For OLE DB)時，也可以順利無誤地顯示Unicode字元。
    搞錯了工具，有可能INSERT成功了還不自知，或許一開始就被錯誤的觀察結果給耍了，白繞了一大圈。

[2006/12/28 Update]
還有一把歪歪的尺忘了說，.NET Console Application也不Support Unicode，而會以ANSI方式輸出，因此要視OS設定而定；我的Windows預設非Unicode語系當然是BIG5，這讓用Console App寫Test Code的我又多花了半小時...

2.用英文跟老外搭訕前，先確定他懂英文

MS SQL我用了N年，連我的膝蓋都認同N'...'表示的就是NChar字元。這個語法在Oracle 9i也支援， 只可惜處理法則跟想像的很不一樣。即使用N'...'標示的Unicode字串，Oracle 9i資料庫也堅持用自身設定的字元集去解析， 把不認得的字元換成?或?再存入資料庫... 這... 這會不會太雞婆了點? 字串前的N難道是加心酸的?

由於沒想到N'...'在資料庫字元集為BIG5時根本是黑心貨，花了大把時間做的測試純屬虛工。用N'...'測試的結果， 加上幾位有Oracle奮戰朋友的佐證，推導出"除非資料庫字元集改為UTF8，否則NChar就只能接受BIG5字元"的悲觀結論。

(Oracle文件倒是提供了一些替代方案，NCHAR(505055)、UNISTR('\20AC')、chr(14844588 USING NCHAR_CS)，但要把原本簡單的欄位值指定程式改成這副德行，讓人想要寫個"慘"字)

最後因緣際會下，用最原始的.NET Sample再試一次，加上湊巧用QueryExpress的Oracle Driver模式查詢，看到Oracle資料表中出現三頭牛疊羅漢(?是這次拿來測試非BIG5的字元)的一剎那，我當場濕了眼眶~~~

正確來說，Oracle 9i的NChar, NVarchar並不黑心，只是嬌貴的它需要我們留意各項細節， 小心對待才能正確地儲存資料。以下我歸納出用.NET正確Insert非BIG5字元進Oracle 9i BIG5資料庫的注意事項:

1. 使用cmd.Parameters指定參數，而不要用Ad-Hoc方式組N'...' (2010-10-25更新: 事隔多年，終於找到讓N'...'成功的方法)
   

   OracleConnection cn = new OracleConnection(cnStr);

   OracleCommand cmd = new OracleCommand("Insert Into NCharTest Values (:VC, :NVC)", cn);

   cmd.Parameters.Add("VC", OracleDbType.Varchar2).Value = "牛?";

   cmd.Parameters.Add("NVC", OracleDbType.NVarchar2).Value = "牛?";

   cn.Open();

   cmd.ExecuteNonQuery();

   cn.Close();

   
   
2. 記得明確宣告OracleDbType.NVarchar2(ODP.NET)或OracleType.NVarChar(System.Data.OracleClient)。我同事疏忽的地方便是用了cmd.Parameters.Add("VC","牛?")的寫法，.NET自行決定的OracleDbType顯然不是NVarchar。
3. 據Oracle官方文件表示，10g R2以後的版本已移除對NChar不當解析的問題。