37

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 ?

Dev Blanked
  • 8,555
  • 3
  • 26
  • 32
  • Have a look at this http://stackoverflow.com/questions/17649011/search-for-a-particular-string-in-oracle-clob-column – Jacob Aug 15 '13 at 09:51

4 Answers4

56

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
Don Kirkby
  • 53,582
  • 27
  • 205
  • 286
Nick Krasnov
  • 26,886
  • 6
  • 61
  • 78
  • is this behaviour the same with all other data bases like postgres as well – Dev Blanked Aug 16 '13 at 06:49
  • @DevBlanked PostgreeSQl has `TEXT`(there is no `CLOB`) data type, and [allows](http://www.sqlfiddle.com/#!1/193dd/5) you use comparison operators to compare values of that data type to each other or a string literal. – Nick Krasnov Aug 16 '13 at 07:14
  • 1
    Beware that "Functions with NULL or invalid input values for parameters return a NULL". This may be significant in some cases. – Vadzim Aug 12 '15 at 12:52
10

how about 

select * from table_name where to_char(clob_column) ="test_string"
Manish Puri
  • 422
  • 4
  • 6
1

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';
Vrashabh Irde
  • 14,129
  • 6
  • 51
  • 103
0

So do not. Because if you want to compare the data - then it may be a situation where one value: NULL and the second EMPTY_CLOB And it is for this method different matter! It returns -1, although in terms of data - both values ​​should not contain. Would be correct:

dbms_lob.compare (NVL (AUDIT_PAYLOAD_TEXT_DEC, Empty_Clob ()),  NVL (AUDIT_PAYLOAD_TEXT, Empty_Clob ()))
splrs
  • 2,424
  • 2
  • 19
  • 29
Sergey
  • 1