Querying oracle clob column
database, hibernate, jpa, oracle, sql
Solution
Yes, it's not allowed (this restriction does not affect `CLOB`s comparison in PL/SQL) to use comparison operators like `=`, `!=`, `<>` and so on in SQL statements, when trying to compare two `CLOB` columns or `CLOB` column and a character literal, like you do. To be able to do such comparison in SQL statements, dbms_lob.compare() function can be used.
select *
from aTable
where dbms_lob.compare(aClobColumn, 'value') = 0
In the above query, the `'value'` literal will be implicitly converted to the `CLOB` data type. To avoid implicit conversion, the `'value'` literal can be explicitly converted to the `CLOB` data type using `TO_CLOB()` function and then pass in to the `compare()` function:
select *
from aTable
where dbms_lob.compare(aClobColumn, to_clob('value')) = 0
Problem
I have a table with a clob column. Searching based on the clob column content needs to be performed. However `select * from aTable where aClobColumn = 'value';` fails but ``` select * from aTable where aClobColumn like 'value'; ``` seems to workfine. How does oracle handle filtering on a clob column. Does it support only the 'like' clause and not the =,!= etc. Is it the same with other databases like mysql, postgres etc Also how is this scenario handled in frameworks that implement JPA like hibernate ?