What is the best way to search an Oracle CLOB column?

Question

I need to search a CLOB column and am looking for the best way to do this, I've seen variants online of using the DBMS_LOB package as well as using something called Oracle Text. Can someone provide a quick example of how to do this?

Answer 1

Oracle Text indexing is the way go. You can use either CONTEXT or CTXRULE index. CONTEXT can be used on unstructured document where CTXRULE is more helpful on structured documents.

This link will provide more info the index types & syntax.

The most important factor you need to consider is LEXER & STOPLIST.

You can also read the posts on asktom.oracle.com

http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:5533095920114

Answer 2

What is in your CLOB and what are you searching for ?

Oracle Text is good if you are searching for words or phrases (which is probably what you have in a CLOB). Sometimes you'll store something 'strange' in a CLOB, like XML or the return value of a web-service call and that might be a different kettle of fish.