接獲報案,其他系統匯入簡體中文資料寫入 Oracle 資料表後部分字元無法顯示。 追查轉擋程式是使用 System.Data.OracleClient 執行 UPDATE Table SET C2=N'...' WHERE C1=1 進行更新。 (註:N'...' 寫法的術語為 NChar Literal String) 詭異的是,轉入的簡體字串,部分字元被轉成繁體,部分則變成方框或空白!

之前學過用 ORA_NCHAR_LITERAL_REPLACE 小密技確保 OralceCommand.CommandText N'...' 內含 Unicode 字元正確解讀,當時曾反覆驗證, 也在工作環境應用多時,不為何冒出怪問題。

抽出問題程式邏輯試著重現問題,經過反覆測試比對,觀察到以下現象:

  1. ORA_NCHAR_LITERAL_REPLACE 這招只對 Unmanaged ODP.NET 有效,System.Data.OracleClient 及 Managed ODP.NET 不適用。
  2. 將簡體文字寫入採 ZHT16MSWIN950 編碼之 VARCHAR2 欄位,Oracle 會嘗試將其轉為繁體中文,但不是所有字元都能轉換 (過去研究 N'...' 時多聚焦 BIG5 難字,沒注意過簡體字元)

我設計了以下程式重現問題,資料庫伺服器為 Oracle 11.2,NText 欄位是 NVARCHAR2(32),AText 為 VARCHAR2(16), 客戶端的 NLS_LANG 設定為 TRADITIONAL CHINESE_TAIWAN.ZHT16MSWIN950。 執行 UPDATE SET NText = N'赵犇',若用 System.Data.OracleClient 會變成 '趙 '。 (簡體「赵」被轉換成繁體「趙」,「犇」字則無法顯示)。

#define MSORA

using System;
using System.Diagnostics;
using System.Text;
using System.Threading;
#if MSORA
using System.Data.OracleClient;
#else 
using Oracle.DataAccess.Client;
#endif

namespace TestOraUnicode
{
    class Program
    {
        static string cs = "data source=ora;user id=user;password=pwd";

        static void Main(string[] args)
        {
            if (args.Length > 0 && args[0] == "test")
            {
                System.Environment.SetEnvironmentVariable("ORA_NCHAR_LITERAL_REPLACE", "TRUE");
                Console.WriteLine(typeof(OracleConnection).Assembly.FullName);
                using (var cn = new OracleConnection(cs))
                {
                    cn.Open();

                    var cmd = cn.CreateCommand();
                    try
                    {
						//警告:組裝SQL指令有SQL Injection風險,禁止在字串中夾帶外部傳入內容
                        cmd.CommandText = 
                            $"update jefftest set atext='赵犇', ntext= N'赵犇', timestmp={DateTime.Now:fff} where idx=1";
                        Console.WriteLine(cmd.CommandText);
                        cmd.ExecuteNonQuery();
                        cmd.CommandText = "update jefftest set atext=:at, ntext=:nt, timestmp=:ts where idx=2";
                        Thread.Sleep(100);
#if MSORA
                        cmd.Parameters.Add("at", OracleType.VarChar).Value = $"赵犇";
                        cmd.Parameters.Add("nt", OracleType.NVarChar).Value = $"赵犇";
                        cmd.Parameters.Add("ts", OracleType.Int32).Value = DateTime.Now.Millisecond;
#else
                        cmd.BindByName = true;
                        cmd.Parameters.Add("at", OracleDbType.Varchar2).Value = $"赵犇";
                        cmd.Parameters.Add("nt", OracleDbType.NVarchar2).Value = $"赵犇";
                        cmd.Parameters.Add("ts", OracleDbType.Int32).Value = DateTime.Now.Millisecond;
#endif
                        Console.WriteLine(cmd.CommandText);
                        cmd.ExecuteNonQuery();
                        cmd.CommandText = "select * from jefftest";
                        cmd.Parameters.Clear();
                        var dr = cmd.ExecuteReader();
                        while (dr.Read())
                        {
                            Console.WriteLine($"{dr["IDX"]},{dr["ATEXT"]},{dr["NTEXT"]}");
                        }
                        Console.WriteLine("Done!");
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine("ERROR!");
                        Console.WriteLine($"Command={cmd.CommandText}");
                        Console.WriteLine(ex);
                    }
                }
                return;
            }
		}
	}
}	

實測結果如下:(以 #define MSORA 切換 Oracle 元件,TestOdpNet.exe 使用 Unmanaged ODP.NET,TestSysDataOra 使用 System.Data.OracleClient)

至此,得到兩點結論:

  1. 遇到 Unicode 簡體中文存入 TRADITIONAL CHINESE_TAIWAN.ZHT16MSWIN950 編碼情境,Oracle 會試著將簡體字元對映成繁體字元。 但不是所有字元都能轉換而有部分字元無法顯示。此點亦可由「赵犇」寫入 AText VARCHAR2(16) 變成「趙 」證實。
    改用 Oracle SQL Developer 做測試, 也可觀察到 Oracle 的簡繁轉換行為:
  2. ORA_NCHAR_LITERAL_REPLACE 這招對 System.Data.OracleClient 無效。

經過研究,找到將 NLS_LANG 改為 TRADITIONAL CHINESE_TAIWAN.UTF8 的 Workaround,可讓 System.Data.OracleClient 正確處理 N'赵犇' 寫入:

但是,NLS_LANG 這招對 Managed ODP.NET 則無效, 依據 StackOverflow 討論, Managed ODP.NET 不支援 NLS_LANG,以 .NET 的語系為準,OracleGlobalization 也不提供 ClientCharacterSet 設定。請乖乖改用 OracleParameter。

雖然找到 NLS_LANG 這招解決問題,基於 System.Data.OracleClient 已被微軟宣告過時不建議使用,最後我們還是修改程式,改用 Unmanaged ODP.NET 解決問題。

Notes of simplified Chinese chars conversion behavior of Oracle client and how to overcome the issue of System.Data.OracleClient doesn't support ORA_NCHAR_LITERAL_REPLACE.


Comments

Be the first to post a comment

Post a comment


10 + 2 =