ORA-00932: inconsistent datatypes: expected - got CLOB

ORA-00932: inconsistent datatypes: expected - got CLOB

Postby efcoyote » Tue 11 Nov 2014 11:40

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?
efcoyote
 
Posts: 5
Joined: Tue 19 Aug 2014 12:22

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

Postby MariiaI » Wed 12 Nov 2014 10:45

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();
MariiaI
Devart Team
 
Posts: 1472
Joined: Mon 13 Feb 2012 08:17

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

Postby efcoyote » Wed 12 Nov 2014 15:13

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.
efcoyote
 
Posts: 5
Joined: Tue 19 Aug 2014 12:22

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

Postby MariiaI » Thu 13 Nov 2014 11:45

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.
MariiaI
Devart Team
 
Posts: 1472
Joined: Mon 13 Feb 2012 08:17


Return to dotConnect for Oracle