3

I have an entity that has a CLOB attribute:

public class EntityS {
  ...
  @Lob
  private String description;
}

To retrieve certain EntityS from the DB we use a CriteriaQuery where we need the results to be unique, so we do:

query.where(builder.and(predicates.toArray(new Predicate[predicates.size()]))).distinct(true).orderBy(builder.asc(root.<Long> get(EntityS_.id)));

If we do that we get the following error:

ORA-00932: inconsistent datatypes: expected - got CLOB

I know that's because you cannot use distinct when selecting a CLOB. But we need the CLOB. Is there a workaround for this using CriteriaQuery with Predicates and so on?

We are using an ugly workaround getting rid of the .unique(true) and then filtering the results, but that's crap. We are using it only to be able to keep on developing the app, but we need a better solution and I don't seem to find one...

diminuta
  • 1,545
  • 8
  • 32
  • 55

2 Answers2

1

In case you are using Hibernate as persistence provider, you can specify the following query hint:

query.setHint(QueryHints.HINT_PASS_DISTINCT_THROUGH, false);

This way, "distinct" is not passed through to the SQL command, but Hibernate will take care of returning only distinct values.

See here for more information: https://thoughts-on-java.org/hibernate-tips-apply-distinct-to-jpql-but-not-sql-query/

Olaf Ziems
  • 69
  • 8
0

Thinking outside the box - I have no idea if this will work, but perhaps it is worth a shot. (I tested it and it seems to work, but I created a table with just one column, CLOB data type, and two rows, both with the value to_clob('abcd') - of course it should work on that setup.)

To de-duplicate, compute a hash of each clob, and instruct Oracle to compute a row number partitioned by the hash value and ordered by nothing (null). Then select just the rows where the row number is 1. Something like below (t is the table I created, with one CLOB column called c).

I expect that execution time should be reasonably good. The biggest concern, of course, is collisions. How important is it that you not miss ANY of the CLOBs, and how many rows do you have in the base table in the first place? Is something like "one chance in a billion" of having a collision acceptable?

select c
from (
select c, row_number() over (partition by dbms_crypto.hash(c, 3) order by null) as rn
from t
)
where rn = 1;

Note - the user (your application, in your case) must have EXECUTE privilege on SYS.DBMS_CRYPTO. A DBA can grant it if needed.