searching a clob field

Hi all,

I am trying to search for a string in the clob field of one my tables. Previously is used to employ the 'like' keyword for all the varchar fields and was working fine. Now when I am trying to do so with the clob field I am getting an exception like 'inconsistent datatypes'.

Can anyone, give me an example query or anything like that. Any help will be greatly appreciated.

Thank you

I just had to port an access/coldfusion application to oracle/php. I created a clob in oracle8i for each of the memo fields in access. Now I cannot search the clob with this code:

WHERE upper(partners) like '%$search_keyword%'

.. i get the inconsistent data type error. So I changed the search to do this:

WHERE CONTAINS (partners, '%$search_keyword%') > 0

.. this time I get an interMedia Text error: DRG-10599: column is not indexed error.

Any simple help to be able to search this type of field?

Try to use dbms_lob.instr() function - it works fine for me, it is case sensitive though and less flexible than like operator...

Thanks a lot. I have been looking for this for quite some time now. take care