Page 1 of 1
ORA-00932: inconsistent datatypes: expected - got CLOB
Posted: Tue 11 Nov 2014 11:40
by 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?
Re: ORA-00932: inconsistent datatypes: expected - got CLOB
Posted: Wed 12 Nov 2014 10:45
by 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();
Re: ORA-00932: inconsistent datatypes: expected - got CLOB
Posted: Wed 12 Nov 2014 15:13
by 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.
Re: ORA-00932: inconsistent datatypes: expected - got CLOB
Posted: Thu 13 Nov 2014 11:45
by 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.