CODEWITHSUNDEEP
.netasp.net-mvcoracle-database
Sasan
Sasan

Reputation: 614

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

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

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

My query is here

availableTasks.Where(task => 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.

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)

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

Upvotes: 4

Views: 4093

Answers (4)

Ram Chhabra
Ram Chhabra

Reputation: 441

select emp.emp_id from employee emp where emp.NAME like '%\\_%' ESCAPE '\\';

Upvotes: 0

ManPL
ManPL

Reputation: 11

We had the same problem

Resolved by changing parameter CURSOR_SHARING to EXACT

Upvotes: 1

Tetsuya Yamamoto
Tetsuya Yamamoto

Reputation: 24957

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()));

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)

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

Upvotes: 6

Marmite Bomber
Marmite Bomber

Reputation: 21075

The ESCAPE in the SQL statement you posted is fine - ESCAPE '\'

But this is the query text logged by LINQ

The best way to see the real query statement send to DB is to activate a 10046 trace.

 ALTER SESSION SET tracefile_identifier = escape;
 alter session set events '10046 trace name context forever, level 12';

in your connection and run the statement.

You will find a file such as xxx_ora_NNNN_ESCAPE.trc in the trace folde of the DB Server.

I suspect you will see a statement using something like 

escape '\\'

Upvotes: 1

Related Questions