We help IT Professionals succeed at work.

CLOB field in sql 'WHERE clause' gives ORA-00932: inconsistent datatypes

javaq092999
javaq092999 asked
on
Medium Priority
4,743 Views
Last Modified: 2012-06-27
How to use CLOB fields in WHERE clause?
---------------------------------------

create table t (id number, data clob);

...properly inserted number of records having clob data...

select id from t where data like '%something%';

The above gives ORA-00932: inconsistent datatypes

How to use CLOB fields in WHERE clause?

I am using Oracle8.1.6 Windows 2000 JDK1.3 JDBC Thin/OCI8 drivers

--Many Thanks
Comment
Watch Question

You have to use the build_in package DBMS_LOB.

Within DBMS_LOB you have procedures/functions
like INSTR, TRIM and SUBSTR to manipulatie the
object.

Commented:
This should work:

select id
  from t
 where dbms_lob.instr(data,'something')>0;

Author

Commented:
Sorry  mozartny as I already solved the problem and I feel that clue provided by vanmeerendonk is what you have re-written more acurately. I forgot to close this question when I found the solution. I wish I could have given you both the points. Thanks you both specially "mozartny".

Author

Commented:
See my comments above. --javaq