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()
方法的实现似乎无法正常工作,它将生成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屋!