Querying oracle clob column
Solution 1
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
Solution 2
how about
select * from table_name where to_char(clob_column) ="test_string"
Solution 3
Clob's are large datatypes that can store massive data and hence many operators that support varchar operations will not work on Clob, but in PL/SQL some of them do like mentioned here: http://docs.oracle.com/cd/B19306_01/appdev.102/b14249/adlob_sql_semantics.htm#g1016221
As you can see in the table Like
is supported in both Sql and pl/sql for clobs, but =
is not supported in SQL, but is in pl/sql
If you really need to you could convert to varchar in sql and compare like Tom Kyte mentions here like this: http://sqlfiddle.com/#!4/1878f6/1
select * from aTable where dbms_lob.substr( aClobColumn , length(aClobColumn), 1 )='value';
Dev Blanked
Updated on June 12, 2020Comments
-
Dev Blanked almost 4 years
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 ?