"ORA-00936: missing expression" on 9.6.570

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
erolg
Posts: 3
Joined: Mon 27 Aug 2018 05:34

"ORA-00936: missing expression" on 9.6.570

Post by erolg » Mon 27 Aug 2018 06:46

Hi,

the ef core linq expression

Code: Select all

dbContext.Class2.Where(two => 
                    two.Test && 
                    two.Threes.Any(three => three.Test && !three.IsDeleted ))
                .Select(o =>
                    new { 
                        o.OneId, 
                        NoFours = o.Threes.Any(three => 
                            three.Test &&
                            !three.IsDeleted &&
                            !three.Fours.Any(four =>
                                    !four.IsDeleted &&
                                    four.Test &&
                                    four.FiveId != null &&
                                    !four.Five.IsDeleted))})
                .ToList()
results to the generated sql

Code: Select all

SELECT "two"."OneId", CASE
    WHEN (
        SELECT CASE
            WHEN EXISTS (
                SELECT 1
                FROM "Class3" "three0"
                WHERE ((("three0"."Test" = 1) AND ("three0"."IsDeleted" = 0)) AND NOT EXISTS (
                    SELECT 1
                    FROM "Class4" "four"
                    LEFT JOIN "Class5" "four.Five" ON "four"."FiveId" = "four.Five"."Id"
                    WHERE (((("four"."IsDeleted" = 0) AND "four"."Test") AND "four"."FiveId" IS NOT NULL) AND ("four.Five"."IsDeleted" = 0)) AND "three0"."Id" = "four"."Class3Id")) AND "two"."Id" = "three0"."TwoId")
            THEN 1 ELSE 0
        END
        FROM DUAL
    ) = True
    THEN 1 ELSE 0
END AS "NoFours"
FROM "Class2" "two"
WHERE ("two"."Test" = 1) AND EXISTS (
    SELECT 1
    FROM "Class3" "three"
    WHERE (("three"."Test" = 1) AND ("three"."IsDeleted" = 0)) AND "two"."Id" = "three"."TwoId")
which throws the error "ORA-00936: missing expression". There are two errors in the statement.

First error in line 11:

WHERE (((("four"."IsDeleted" = 0) AND "four"."Test") AND "four"."FiveId" IS NOT NULL) AND ("four.Five"."IsDeleted" = 0)) AND "three0"."Id" = "four"."Class3Id")) AND "two"."Id" = "three0"."TwoId")

should be

WHERE (((("four"."IsDeleted" = 0) AND ("four"."Test" = 1)) AND "four"."FiveId" IS NOT NULL) AND ("four.Five"."IsDeleted" = 0)) AND "three0"."Id" = "four"."Class3Id")) AND "two"."Id" = "three0"."TwoId")


Second error in line 15:

FROM DUAL
) = True
THEN 1 ELSE 0

should be

FROM DUAL
) = 1
THEN 1 ELSE 0

Democode can be downloaded here

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: "ORA-00936: missing expression" on 9.6.570

Post by Shalex » Tue 28 Aug 2018 17:03

Thank you for your report. We have reproduced the issue and will notify you when it is fixed.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: "ORA-00936: missing expression" on 9.6.570

Post by Shalex » Wed 07 Nov 2018 18:38

* The bug with throwing "ORA-00936: missing expression" in EF Core is fixed.
* The bug with using parentheses in binary expressions in EF Core is fixed.

We will notify you when the new public build of dotConnect for Oracle is available for download.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: "ORA-00936: missing expression" on 9.6.570

Post by Shalex » Wed 07 Nov 2018 18:42

Nuget package with the fix can be downloaded from https://www.devart.com/pub/nuget_oracle_9_6_630.zip.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: "ORA-00936: missing expression" on 9.6.570

Post by Shalex » Thu 29 Nov 2018 16:00

New build of dotConnect for Oracle 9.6.646 is available for download: viewtopic.php?f=1&t=38123.

Vidya
Posts: 1
Joined: Thu 19 Sep 2019 09:55

Re: "ORA-00936: missing expression" on 9.6.570

Post by Vidya » Thu 19 Sep 2019 09:59

Still getting Missing Expression on 9.6.646 on linq queries

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: "ORA-00936: missing expression" on 9.6.570

Post by Shalex » Fri 20 Sep 2019 18:01

Vidya wrote: Thu 19 Sep 2019 09:59Still getting Missing Expression on 9.6.646 on linq queries
Please upgrade to the newest (9.8.838 12-Sep-19) build of dotConnect for Oracle.

If the bug persists, send us a small test project for reproducing the issue.

Post Reply