Querying oracle clob column

Question

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 ?

Answer 1

Yes, it's not allowed (this restriction does not affect CLOBs 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 

Answer 2

how about

select * from table_name where to_char(clob_column) ="test_string"