Finding specific content in Oracle CLOB column

Some time ago, I faced this error in Oracle SQL statement:

ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 5677, maximum: 4000)

At that time, I was trying to perform a select statement to obtain all rows containing some content in a CLOB column. Something like that:

select * 
from clob_table 
where to_char(clob_column) like '%some content%'

The problem is that the maximum amount of characters in a Oracle varchar (which happens when you use to_char function in CLOB column) is 4000, but some rows in my table had more than that.

But don’t worry! If you are facing similar problem, the solution is very simple: just use the dbms_lob.instr() function. Just like this:

select * 
from clob_table 
where dbms_lob.instr(clob_column, 'some content') >= 1

It’s all for today. See you soon.