ORA-00932: inconsistent datatypes: expected - got CLOB

[efcoyote]

I have a table with one CLOB-column and the following simplified query:

Code: Select all

var list = context.Entities.Select(p => p.ClobProperty).Distinct().ToList();

The result is a "ORA-00932: inconsistent datatypes: expected - got CLOB" exception. I know that Oracle doesn't support comparing CLOB-columns. Therefore you also not able to use Union, because UNION ALL and DISTINCT are generated.

Code: Select all

var list = context.Entities.Union(context.Entities).ToList();

This is a hard limitation for all tables with CLOB-columns. Is there any Workaround?

[MariiaI]

As a workaround you can rewrite your query in the following way:

Code: Select all

var list = context.Entities.Select(p => p.ClobProperty.Substring(0, 4000)).Distinct().ToList();

[efcoyote]

For that special simplified query it will work (with limitations). But you don't need CLOB when only checking first 4000 chars. I found a raw sql solution, where to first execute a UNION ALL and continue with DINSTINC over a hash of clob column. Unfortunally I see no way to implement that with LINQ.

[MariiaI]

I found a raw sql solution, where to first execute a UNION ALL and continue with DINSTINC over a hash of clob column. Unfortunally I see no way to implement that with LINQ.

We would be very much obliged to you, if you share your solution. Probably, we will provide you with an alternative way (with LINQ) or your solution will be useful to other users.