ORA-00932: inconsistent datatypes: expected - got NCLOB

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
Velu
Posts: 20
Joined: Wed 02 Aug 2017 14:01

ORA-00932: inconsistent datatypes: expected - got NCLOB

Post by Velu » Mon 23 Dec 2019 09:24

Hi,

We are using .Netcore 3.1

Devart: 9.9.892 Internal build

We have facing this issue while getting value from the database.

COLUMN NAME AND DATATYPE:
discriminator nvarchar2(1000)

Query generated by DEVART IS :
SELECT a.Id, a.CreationTime, a.CreatorUserId, a.Discriminator, a.Name, a.TenantId, a.Value, a.EditionId
FROM AbpFeatures a
WHERE (a.Discriminator = TO_NCLOB('EditionFeatureSetting')) AND ((a.EditionId = '1') AND (a.Name = 'Default'))
FETCH FIRST 1 ROWS ONLY


at this point, we have the issue.

Please help us to solve this.
Thanks

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

Re: ORA-00932: inconsistent datatypes: expected - got NCLOB

Post by Shalex » Thu 26 Dec 2019 19:22

The model mapping and LINQ query are missing in your report.

General recommendations:
1. Specify the type of the column in your mapping, e.g.: [Column(TypeName = "nvarchar2")].
2. NVARCHAR will be used if you set max length for string properties explicitly either via .HasMaxLength(1000) or via [MaxLength(1000)].
3. You can set up your application to use NVARCHAR instead of CLOB by default via config.CodeFirstOptions.UseNonLobStrings=true.

If this doesn't help, send us a test project for reproducing the issue.

Eric_08
Posts: 27
Joined: Wed 11 Jul 2018 21:50

Re: ORA-00932: inconsistent datatypes: expected - got NCLOB

Post by Eric_08 » Thu 26 Dec 2019 19:38

I've ran into the same problem immediately after upgrading to .NET Core 3.1. Using the latest DevArt 9.10 and .NET Core 3.1, I get an error on the discriminator column type. This is the column that's defined by HasDiscriminator() method in EF Core. It would look like this for mapping:

Code: Select all

 
 // Discriminator column
 builder.HasDiscriminator<string>("DISCRIMINATOR")
 
The code started producing SQL using TO_NCLOB() function. Prior to the upgrade, it was using DBMS_LOB.COMPARE.

This seems like a breaking change.

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

Re: ORA-00932: inconsistent datatypes: expected - got NCLOB

Post by Shalex » Sat 28 Dec 2019 14:39

We have reproduced the issue and are investigating it.

sdemir
Posts: 1
Joined: Tue 07 Jan 2020 06:41

Re: ORA-00932: inconsistent datatypes: expected - got NCLOB

Post by sdemir » Tue 07 Jan 2020 06:44

Hi, after upgrade .net core 3.1 and devart 9.10.909 get an error
: Devart.Data.Oracle.OracleException (0x80004005): ORA-00932: inconsistent datatypes: expected - got NCLOB

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

Re: ORA-00932: inconsistent datatypes: expected - got NCLOB

Post by Shalex » Thu 09 Jan 2020 19:02

The bug with using a discriminator in EF Core 3 is fixed.

The internal build with the fix is available at https://download.devart.com/nuget_oracle_9_10_921.zip.

entwicklungsensis
Posts: 17
Joined: Tue 21 Aug 2018 09:47

Re: ORA-00932: inconsistent datatypes: expected - got NCLOB

Post by entwicklungsensis » Thu 13 Feb 2020 10:50

Hi,
we got the same problem. I updated the devart.dotconnect package with the internal build 9.10.921 but the error remains.

The reason is:
linq:

Code: Select all

            var ressource1 = from qryRessourceDetailTyp in ressources
                             where qryRessourceDetailTyp.RessourceDetailTyp.TypComplex.Kennung.Equals("PERS", StringComparison.CurrentCultureIgnoreCase)
                             select qryRessourceDetailTyp;
produces this statement:

Code: Select all

      SELECT "r"."Id", "r"."AuditId", "r"."IdT", "r"."RessourceDetailTypId", "r"."StandortId", "r"."TypT", "t0"."Id" "Id1", "t0"."Aktiv", "t0"."InaktivSeit"
      FROM "Ressource" "r"
      INNER JOIN "RessourceDetailTyp" "r0" ON "r"."RessourceDetailTypId" = "r0"."Id"
      LEFT JOIN (
          SELECT "r1"."Id", "r1"."Bezeichnung", "r1"."Kennung", "r2"."Id" "Id0"
          FROM "RessourceDetailTyp" "r1"
          INNER JOIN "RessourceDetailTyp" "r2" ON "r1"."Id" = "r2"."Id"
          WHERE "r1"."Kennung" IS NOT NULL AND "r1"."Bezeichnung" IS NOT NULL
      ) "t" ON "r0"."Id" = "t"."Id"
      LEFT JOIN (
          SELECT "r3"."Id", "r3"."Aktiv", "r3"."InaktivSeit", "r4"."Id" "Id0"
          FROM "Ressource" "r3"
          INNER JOIN "Ressource" "r4" ON "r3"."Id" = "r4"."Id"
          WHERE "r3"."Aktiv" IS NOT NULL
      ) "t0" ON "r"."Id" = "t0"."Id"
      WHERE UPPER("t"."Kennung") = UPPER(TO_NCLOB('PERS'))
TO_NCLOB is wrong. Kennung is nvarchar2(25). No need to convert to NCLOB.

this linq:

Code: Select all

            var ressource1 = from qryRessourceDetailTyp in ressources
                             where qryRessourceDetailTyp.RessourceDetailTyp.TypComplex.Kennung.Equals("PERS")
                             select qryRessourceDetailTyp;
                             
without

Code: Select all

, StringComparison.CurrentCultureIgnoreCase
produces this statement:

Code: Select all

      SELECT "r"."Id", "r"."AuditId", "r"."IdT", "r"."RessourceDetailTypId", "r"."StandortId", "r"."TypT", "t0"."Id" "Id1", "t0"."Aktiv", "t0"."InaktivSeit"
      FROM "Ressource" "r"
      INNER JOIN "RessourceDetailTyp" "r0" ON "r"."RessourceDetailTypId" = "r0"."Id"
      LEFT JOIN (
          SELECT "r1"."Id", "r1"."Bezeichnung", "r1"."Kennung", "r2"."Id" "Id0"
          FROM "RessourceDetailTyp" "r1"
          INNER JOIN "RessourceDetailTyp" "r2" ON "r1"."Id" = "r2"."Id"
          WHERE "r1"."Kennung" IS NOT NULL AND "r1"."Bezeichnung" IS NOT NULL
      ) "t" ON "r0"."Id" = "t"."Id"
      LEFT JOIN (
          SELECT "r3"."Id", "r3"."Aktiv", "r3"."InaktivSeit", "r4"."Id" "Id0"
          FROM "Ressource" "r3"
          INNER JOIN "Ressource" "r4" ON "r3"."Id" = "r4"."Id"
          WHERE "r3"."Aktiv" IS NOT NULL
      ) "t0" ON "r"."Id" = "t0"."Id"
      WHERE "t"."Kennung" = N'PERS'
      
which is correct.

btw: kennung length 25 is a property in a ComplexType

Please correct this. We are using

Code: Select all

, StringComparison.CurrentCultureIgnoreCase
often in our linq statements.

Regards
Thomas

Post Reply