KB-Oracle 9i NVarchar求生守則
11 | 44,662 |
同事反應,一個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資料庫的注意事項:
- 使用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();
- 記得明確宣告OracleDbType.NVarchar2(ODP.NET)或OracleType.NVarChar(System.Data.OracleClient)。我同事疏忽的地方便是用了cmd.Parameters.Add("VC","牛?")的寫法,.NET自行決定的OracleDbType顯然不是NVarchar。
- 據Oracle官方文件表示,10g R2以後的版本已移除對NChar不當解析的問題。
Comments
# by Anonymous
有個小小的疑問跟本主題無關..<BR/>但我又不知如何請教大大..<BR/>所以在這裡問..<BR/>有打擾之處還請見諒..<BR/><BR/>事情是這樣的..<BR/>前陣子小弟改了windows 2003的密碼..<BR/>結果導致底下的錯誤產生..<BR/>伺服器物件 錯誤 'ASP 0177 : 8000401a' <BR/><BR/>Server.CreateObject 失敗 <BR/><BR/>/xxxx_xx/xxxx_xxxxx.asp, 列32 <BR/><BR/>8000401a <BR/><BR/>在其他的電腦上也有發生相同的問題,<BR/>我同事說重開就好了..<BR/>但這一台..是我重開卻沒好的..<BR/>難道是手氣有差嗎??<BR/>實在是找不出原因= =||<BR/>煩請大大為小弟指引一盏明燈啊~~多謝^ ^<BR/>小弟的MAIL是<BR/>andy.boo@seed.net.tw<BR/>感激不盡
# by 秉宏
真的非常感恩版主寫下這篇文章,真是受益良多。因為我也遇到類似的問題。NLS_LANG 為AMERICAN_AMERICA.US7ASCII。 Oracle 9i 資料庫而且不能改 NLS_LANG ,改了會影響其他的大程式(醫院的醫療系統) 因為在開發網頁程式過程中,一定要用到資料庫的大型物件欄位,像是CLOB、BLOB等。 本來剛開始寫這個網頁程式就有考慮用Oracle提供的ODP.NET或是微軟的Microsoft OLE DB Provider For Oracle,來做為連結公司的Oracle 9i資料庫的引擎,剛開始用的時候寫入都是亂碼,後來只好選擇用早期就有的 MSDAORA 這個OleDb Provider,可以正常寫入、讀取中文程式到Varchar的欄位;當時不考慮用NVarchar的原因,就是你文章所提到的測量的尺不是直的,我是用PL/SQL(類似於TOAD的軟體),來查看、修改Oracle的資料。 所以當我看完並了解你文章的內容之後,我做個簡單的測試,並用你建議的QueryExpress來測量,當我參考你的.NET Sample,來做測試結果就成功了,但是Varchar的欄位還是亂碼,但是沒影響。最主要是我測試寫入NCLOB這個欄位也成功了,也是感動到流淚(能體會你當時的情境)。 問題是上個月遇到的,當時就花了整整七天,一直研究這個問題,找遍各大網站,尤其是大陸的網站,很多人都有遇到和我一樣相同的問題,但是還是沒有研究出來,最後因為案子進度的壓力,不得不放棄研究這塊,只能用Varchar2(4000)先撐著,等進度過了之後在找時間研究。 現在終於找到答案了,非常感恩版主無私的將自己的研究成果,幫助我們這些迷失在 問題漩渦 中很久的人。 感恩 E-mail: jumascould@e-ms.com.tw 來自桃園敏盛醫院的菜鳥工程師 秉宏
# by Jeffrey
To 秉宏, 沒想到有綠遇到同被ORACLE中文問題惡整近月的可憐人,說到感動落淚... 同為天涯淪落人,我非常確定你能了解我的感受~~~ 用慣了親和力十足的SQL,每每要處理ORACLE上的各式疑難雜症,用著簡陋到爆的內建工具(ORACLE! 你的SQL Profiler呢? Query Analyzer呢?),總免不了一肚子牢騷。 不過,我接觸的重要資料庫往往還是採用ORACLE居多,即使它貴到靠北! 我個人認為並非SQL真的只能"撿角",很多時候ORACLE還是佔足了品牌形象的優勢。 記得.COM時代有句名言"No one get fired for buying Cisco!" 在ORACLE身上多少也適用。
# by CK
一個CharacterSet設為ZHT16BIG5的Oracle 9i的資料庫,其中某個Table的NVarchar2欄位完全不接受非BIG5字元 請問您這個db的NLS_NCHAR_CHARACTERSET設定是什麼?
# by Jeffrey
to CK, 剛才查了一下之前做測試的DB, select * from nls_database_parameters where parameter='NLS_NCHAR_CHARACTERSET'結果為'UTF8'
# by Jasper
真的感謝您提供"珍貴"的實戰守則..., 還有想要和您分享一個SQL 工具,那就是Oracle SQL Developer , 官方說會Bundle 至11g , 還有這個工具,就一般Developer 而言,相當相當足夠,況且他還是一套免安裝(Java),免費,跨平台,跨資料庫的好工具...現在的版本為1.5XXX ,相較於1.2而言,真是讚到爆 註記: 1.沒記錯的話,2006年底前Release 第一版 2.真是謝謝您啊,Oracle ,終於有一套像樣的工具了啊 3.其實最近在幫公司評估SQL工具,才找到了這套 分享給您,再一次感恩...:)
# by Jasper
不好意思,再註記一小段,有需要的人,官網就可以直接下載,且教材也相當豐富,還有Flash 教學動畫喔...
# by glenn
感謝版主無私的提供驗證的假設與流程。我也被簡體中文無法寫入varchar2煩惱著....目前也還是....即使改用nvarchar2也是一樣變成大角?,很無言。 oracle提供的sql developer僅能說聊勝於無,third party提供的工具更彈性更方便重要的是更快速,譬如plsql developer,只是需要授權費用。 我用sql developer 1.0.0.15.57,直接INSERT,還是得到大角問號,請問各位大大是否有其他解? 目前資料庫的版本9208,編碼是ZHT16BIG5 & AL16UTF16,有得解嗎?
# by Jeffrey
to glenn, 我尚未找到找到可以直接INSERT N'...'到Oracle 9.2的順手工具,遇必須塞難字進Oracle 9.2 NVARCHAR的場合,多是寫.NET程式配合OracleParameter解決。
# by 絕望生魚片
Oracle characterset US7ASCII 亂碼求生之道 經過測試,Oracle的 server characterset 為 US7ASCII 時 存到 DB 中的 中文字 還是以BIG5碼存入 到VARCHAR欄位中 但是當 DotNET 從 DB取出後,卻變成亂碼 檢查編碼 : select columns, UTL_RAW.cast_to_raw(columns) from table columns UTL_RAW.cast_to_raw(columns) -------------------------------------------------------------------------------------- 成功路200號 A6A8A55CB8F4A2B1A2AFA2AFB8B9 利用中文編碼解析工具 1.32 來解析 UTL_RAW.cast_to_raw(columns) 轉出來的編碼是 BIG5 (由此說明當初INSERT進去的還被用BIG5碼塞近去了....) 好吧~進入主題 開始亂搞吧 select UTL_RAW.cast_to_nvarchar2(UTL_RAW.CONVERT(UTL_RAW.cast_to_raw(columns),'TRADITIONALCHINESE_TAIWAN.AL16UTF16','TRADITIONAL.CHINESE_TAIWAN.ZHT16MSWIN950')) as DD from table 把資料庫中的編碼當成 TRADITIONAL.CHINESE_TAIWAN.ZHT16MSWIN950 然後把它(這畜牲)轉成 TRADITIONALCHINESE_TAIWAN.AL16UTF16 好了從SQLPLUS查出來是 ??????? 看來很正常 Dim DS As DataSet, DS1 As DataSet Dim sss As String = "select UTL_RAW.cast_to_nvarchar2(UTL_RAW.CONVERT(UTL_RAW.cast_to_raw(columns),'TRADITIONALCHINESE_TAIWAN.AL16UTF16','TRADITIONAL.CHINESE_TAIWAN.ZHT16MSWIN950')) as DD from table" DS1 = CreateDataset(sss, "aa") 'CreateDataset 函數自己寫的 SSS為SQL SELECT COMMAND STRING , aa為自定己亂義的DATATABLENAME Dim DD As String = DS1.Tables(0).Rows(0).Item(0) Dim bb() As Byte bb = Encoding.Unicode.GetBytes(DS1.Tables(0).Rows(0).Item(0)) Response.Write(DD + " " + BitConverter.ToString(bb) + " ") 從網頁上查出來果然正常了 成功路200號 10-62-9F-52-EF-8D-12-FF-10-FF-10-FF-5F-86 利用中文編碼解析工具 1.32 來解析 10-62-9F-52-EF-8D-12-FF-10-FF-10-FF-5F-86 為 UTF16編碼 使用函數: 1. UTL_RAW.cast_to_nvarchar2 2. UTL_RAW.CONVERT 3. UTL_RAW.cast_to_raw PS:其他編碼沒有測試過...因該是一樣的吧 Jeffrey老大~您的BLOG 我可以加入我的參考LINK網頁嗎?參考您的太多篇了...我也忘了參考幾篇了...才搞出這招強制轉NVARCHAR的方式...感謝您啦 不可以我就拿掉了... 參考Link:</div> <a href="http://psoug.org/reference/utl_raw.html">Oracle UTL_RAW</a> <a href="http://download.oracle.com/docs/html/A96196_01/ap_urpkg.htm">Oracle UTL_RAW Package</a> <a href="http://www.java2s.com/Tutorial/VB/0140__Development/ConvertstringtoandbackUTF8UTF7UnicodeandUTF32.htm">.Convert string to and back: UTF8,UTF7,Unicode and UTF32</a> <a href="http://blog.darkthread.net/post-2006-12-27-kb-oracle-9i-nvarchar.aspx">KB-Oracle 9i NVarchar求生守則 (黑暗執行緒)</a>
# by Lolota
今天搞了一天, 看到黑大這篇文章, 原來....我眼殘的把Oracle Provider For OLE DB看成Microsoft OLE DB Provider For Oracle....讓我like查詢都找不到正確資料..感蝦!! Or2...