ORA-01425:转义字符必须是长度为1的字符串 [英] ORA-01425: escape character must be character string of length 1

问题描述

我们正在使用可与.net(MVC)和Oracle数据库配合使用的软件.之后，我们不得不在新服务器中安装新版本的Oracle(12.1.0)，我们在搜索时会遇到此错误，而在12.2.0版中则没有此类错误. 错误:

We are working a software working with .net (MVC) and Oracle database. After that we were obliged to install new version of Oracle (12.1.0) in new server we face with this error while searching while we didn't have such error in version 12.2.0. Error :

ORA-01425:转义字符必须是长度为1的字符串

ORA-01425: escape character must be character string of length 1

我的查询在这里

availableTasks.Where(task => task.Document.RegistrationNumber.Contains(searchkeyWord) || task.WorkflowNumber.Contains(searchkeyWord) || task.Description.Contains(searchkeyWord));

值得一提的是，当我删除task.Document.RegistrationNumber.Contains(searchkeyWord)或task.WorkflowNumber.Contains(searchkeyWord) || task.Description.Contains(searchkeyWord)时，它可以正常工作.

it is worth mentioning that when i remove task.Document.RegistrationNumber.Contains(searchkeyWord) or task.WorkflowNumber.Contains(searchkeyWord) || task.Description.Contains(searchkeyWord) it works perfectly.

我的查询输出来自Linq:

My query output from Linq:

SELECT 
"GroupBy1"."A1" AS "C1"
FROM ( SELECT 
    COUNT(1) AS "A1"
    FROM   (SELECT "Extent1"."Id" AS "Id1", "Extent1"."Document_Id" AS "Document_Id", "Extent1"."Member_Id" AS "Member_Id", "Extent1"."Command_Id" AS "Command_Id", "Extent1"."InitialTask_Id" AS "InitialTask_Id", "Extent1"."Sender_Id" AS "Sender_Id", "Extent1"."Receiver_Id" AS "Receiver_Id", "Extent1"."Department_Id" AS "Department_Id", "Extent1"."PreviousTask_Id" AS "PreviousTask_Id", "Extent1"."OpeningTime" AS "OpeningTime", "Extent1"."ClosingTime" AS "ClosingTime", "Extent1"."IsDone" AS "IsDone", "Extent1"."Description" AS "Description", "Extent1"."MetaData" AS "MetaData", "Extent1"."WorkflowNumber" AS "WorkflowNumber", "Extent1"."Comment" AS "Comment", "Extent2"."Id" AS "Id2", "Extent2"."PortCommunity_Id" AS "PortCommunity_Id", "Extent2"."Accessibility_Id" AS "Accessibility_Id", "Extent2"."IssuedUser_Id" AS "IssuedUser_Id", "Extent2"."ChangingTime" AS "ChangingTime", "Extent2"."RegistrationNumber" AS "RegistrationNumber", "Extent2"."RegistrationDate" AS "RegistrationDate", "Extent2"."SequenceNumber" AS "SequenceNumber", "Extent2"."DocumentStatus" AS "DocumentStatus", "Extent2"."DocumentType" AS "DocumentType"
        FROM  "KISH"."Task" "Extent1"
        INNER JOIN "KISH"."Document" "Extent2" ON "Extent1"."Document_Id" = "Extent2"."Id"
        WHERE ("Extent1"."IsDone" <> 1) ) "Filter1"
    LEFT OUTER JOIN "KISH"."User" "Extent3" ON "Filter1"."Receiver_Id" = "Extent3"."Id"
    WHERE (((("Filter1"."Member_Id" = :p__linq__0) AND ("Filter1"."Command_Id" = :p__linq__1)) OR ("Filter1"."Command_Id" <> :p__linq__2)) AND ("Filter1"."PortCommunity_Id" = :p__linq__3) AND ((:p__linq__4 <> 1) OR ("Filter1"."Receiver_Id" IS NULL) OR ("Filter1"."Receiver_Id" = :p__linq__5)) AND ( EXISTS (SELECT 
        1 AS "C1"
        FROM "KISH"."Permission" "Extent4"
        WHERE (("Extent4"."User_Id" = :p__linq__6) AND ("Extent4"."Member_Id" = :p__linq__7) AND ("Extent4"."PortCommunity_Id" = :p__linq__8) AND ( EXISTS (SELECT 
            1 AS "C1"
            FROM "KISH"."RoleCommand" "Extent5"
            WHERE (("Extent4"."Role_Id" = "Extent5"."Role_Id") AND ("Extent5"."Command_Id" = "Filter1"."Command_Id"))
        )) AND (("Extent4"."Department_Id" IS NULL) OR ("Filter1"."Department_Id" IS NULL) OR ("Extent4"."Department_Id" = "Filter1"."Department_Id") OR (("Extent4"."Department_Id" IS NULL) AND ("Filter1"."Department_Id" IS NULL))))
    )) AND (("Filter1"."RegistrationNumber" LIKE :p__linq__9 ESCAPE '\') OR ("Filter1"."WorkflowNumber" LIKE :p__linq__10 ESCAPE '\') OR ("Filter1"."Description" LIKE :p__linq__11 ESCAPE '\')))
)  "GroupBy1"SQL: 
SQL: -- p__linq__0: 'db717061-06f1-4235-8413-1d76f65ba80f' (Type = Binary, IsNullable = false)
SQL: -- p__linq__1: '19ccc777-634f-43c6-a5d3-037ae78be91c' (Type = Binary, IsNullable = false)
SQL: -- p__linq__2: '19ccc777-634f-43c6-a5d3-037ae78be91c' (Type = Binary, IsNullable = false)
SQL: -- p__linq__3: '04922971-1ea5-4f76-8de6-13b6e460364b' (Type = Binary, IsNullable = false)
SQL: -- p__linq__4: 'True' (Type = Decimal, IsNullable = false)
SQL: -- p__linq__5: '1d0c0570-9dd0-4aa4-92d7-8055b567c351' (Type = Binary, IsNullable = false)
SQL: -- p__linq__6: '1d0c0570-9dd0-4aa4-92d7-8055b567c351' (Type = Binary, IsNullable = false)
SQL: -- p__linq__7: 'db717061-06f1-4235-8413-1d76f65ba80f' (Type = Binary, IsNullable = false)
SQL: -- p__linq__8: '04922971-1ea5-4f76-8de6-13b6e460364b' (Type = Binary, IsNullable = false)
SQL: -- p__linq__9: '%30%' (Type = Object)
SQL: -- p__linq__10: '%30%' (Type = Object)
SQL: -- p__linq__11: '%30%' (Type = Object)

我完全被这个问题弄糊涂了.我不知道它是针对其他Oracle版本还是其他版本.

I completely confused by this problem. I don't know it is for different Oracle version or something else.

推荐答案

在某些情况下直接处理System.String参数时，使用Entity Framework ODP的LINQ中的Contains()方法的实现似乎无法正常工作，它将生成在PL/SQL查询中使用ESCAPE '\'语句，这将引发ORA-01425错误(如

The implementation of Contains() method in LINQ with Entity Framework ODP seems not working properly when handling System.String argument directly in certain situations, it will generate LIKE with ESCAPE '\' statement in PL/SQL query which will throw ORA-01425 error (as shown in similar issue here). To mitigate this behavior, just use Trim() inside Contains() method against string argument:

availableTasks.Where(task => task.Document.RegistrationNumber.Contains(searchkeyWord.Trim()) 
                     || task.WorkflowNumber.Contains(searchkeyWord.Trim()) 
                     || task.Description.Contains(searchkeyWord.Trim()));

Trim()方法将生成 INSTR 和TRIM语句(或LTRIM-RTRIM对)，用ESCAPE语句替换LIKE，如下所示:

The Trim() method will generate INSTR and TRIM statements (or LTRIM-RTRIM pairs) as replacement of LIKE with ESCAPE statements, as in example below:

SELECT <snip> FROM <snip> WHERE [condition] AND ((NVL(INSTR("Filter1"."RegistrationNumber", TRIM(:p__linq__9)), 0)) > 0) 
OR ((NVL(INSTR("Filter1"."WorkflowNumber", TRIM(:p__linq__10)), 0)) > 0)
OR ((NVL(INSTR("Filter1"."Description", TRIM(:p__linq__11)), 0)) > 0)

通过用TRIM实现INSTR，在处理包含System.String值的LINQ生成的参数(:p__linq__XX)时，查询将平稳运行.

By implementing INSTR with TRIM, the query will run smoothly when handling LINQ generated parameters (:p__linq__XX) containing System.String value.

这篇关于ORA-01425:转义字符必须是长度为1的字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！