【茶包射手日記】Oracle 寫入 N'...' 簡體變繁體

接獲報案，其他系統匯入簡體中文資料寫入 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.