網友報案:使用 ODAC112030 與 Visual Studio 建立 Entity Framework Model,在 C# 使用 LINQ 語法.Where(o => o.ColName.Contains(someVar)),被轉換成以下SQL語法:

WHERE ("Extend1"."FOO" LIKE :p__linq__0 ESCAPE '\')

送到Oracle 9i執行(是的,侏儸紀時代的Oracle 9),冒出ORA-01425錯誤「escape character must be character string of length 1」。

爬文找到一些線索,但活化石Oracle 9.2難尋,特別裝了一台VM驗證。經過一番測試,證實遇到的狀況應屬Oracle 9.2 NVARCHAR2欄位處理LIKE ESCAPE子句的特性(或Bug)。以下是我的測試與分析:

使用以下指令建立一個包含NVARCHAR2欄位(FOO)及VARCHAR2欄位(BAR)的測試資料表:

CREATE TABLE BLAH (
    FOO NVARCHAR2(8) NOT NULL,
    BAR VARCHAR2(16) NOT NULL,
    CONSTRAINT PK_BLAH PRIMARY KEY (FOO)
)

在VS2010建立ADO.NET Data Entity Model進行以下測試。依序查詢並顯示Oracle DB版本,執行.Where(o => o.BAR.Contains(keywd)),再執行.Where(o => o.FOO.Contains(keywd)),並用ToTraceString()觀察產生的SQL語法。

            using (var ent = new Ora9Entities())
            {
                var ver = ent.ExecuteStoreQuery<string>(
                    "select banner from v$version").First();
                Console.WriteLine("Version={0}", ver);
                var keywd = "A";
 
                var query = ent.BLAHs.Where(
                    o => o.BAR.Contains(keywd));
                var sql = ((ObjectQuery)query).ToTraceString();
                Console.WriteLine("SQL={0}", sql);
 
                var count = query.Count();
                Console.WriteLine("Count={0}", count);
 
 
                query = ent.BLAHs.Where(
                    o => o.FOO.Contains(keywd));
                sql = ((ObjectQuery)query).ToTraceString();
                Console.WriteLine("SQL={0}", sql);
 
                try
                {
                    count = query.Count();
                    Console.WriteLine("Count={0}", count);
                }
                catch (Exception ex)
                {
                    Console.WriteLine("Error:{0}", 
                        ex.InnerException.Message);
                }
 
            }
            Console.ReadLine();
        }

連接Oracle 9.2.0.8 Server,執行結果如下:

FOO與BAR的查詢都產生LIKE :p__linq__0 ESCAPE '\'語法,但BAR的查詢過關,FOO的查詢噴出ORA-01425錯誤。FOO與BAR差在一個是NVARCHAR2、一個是VARCHAR2。以此原理,直接用SQLPlus測試也能得到同樣結果,FOO LIKE時ESCAPE '\'需改成ESCAPE N'\'才能正常執行(或寫成ESCAPE '\\'也可以):

同樣的測試搬到Oracle 10g Server執行,FOO LIKE查詢不管寫ESCAPE N'\'或ESCAPE '\'都不會出錯。更進一步,我們可以簡化查詢語法,不需任何資料表就能重現同樣問題,突顯關鍵為LIKE比對的對象,型別是否NVARCHAR2,而出錯的狀況只限於Oracle 9.2:

SQL> select 1 from dual where cast ('1' as varchar2(1)) like 'A%' escape '\';
no rows selected

SQL> select 1 from dual where cast ('1' as nvarchar2(1)) like 'A%' escape '\';
select 1 from dual where cast ('1' as nvarchar2(1)) like 'A%' escape '\'
                                                                     *
ERROR at line 1:
ORA-01425: escape character must be character string of length 1

SQL> select 1 from dual where cast ('1' as nvarchar2(1)) like 'A%' escape N'\';
no rows selected

由以上觀察,結論為「Oracle Server 9.2要求NVARCHAR2欄位LIKE時要寫ESCAPE N'\'或ESCAPE '\\'才不致出錯,而Oracle Server 10以上已無此限制」。顯然,ODAC112030的Entity Framework在產生SQL語法時未將Oracle 9i納入考量,造成此一現象。但依Oracle文件,Oracle 11.2 Client連9.2 Server的組合已不非官方支援範圍,Oracle 12 Client更是完全連不上9.2 Server,說OPD.NET 11.2不支援Oracle 9.2並不為過。

網路上找到一些解法,對.Contains(keywd)做一些手腳即可避開這個問題,例如:改寫成.Contains(keywd.Trim())會轉成

WHERE (( NVL(INSTR("Extent1"."FOO", LTRIM(RTRIM(:p__linq__0))), 0) ) > 0)

雖然避開問題,但LIKE變成了對每一筆資料欄位行INSTR,效能可能變差,應用時要當心。另一種做法,是改用Stored Procedure執行所需比對,或改用ExecuteStoredQuery也可納入考量。(再三提醒:如要自行撰寫SQL查詢,LIKE對象請以參數傳入,不要直接組字串,否則一旦產生SQL Injection漏洞,甚至會有家破人亡的風險。)


Comments

Be the first to post a comment

Post a comment


55 - 49 =