ORA-00932: inconsistent datatypes: expected - got NCLOB

[Velu]

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]

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]

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]

We have reproduced the issue and are investigating it.

[sdemir]

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]

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]

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

[Shalex]

We cannot reproduce the issue with .Equals("PERS", StringComparison.CurrentCultureIgnoreCase) using v9.10.921. Please upload a small test project with the corresponding DDL script to some file exchange server (e.g.: www.dropbox.com).

[Shalex]

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

dotConnect for Oracle v9.11.951 is released: viewtopic.php?f=1&t=39884.