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

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开发者_如何学Python DBMS_LOB package as well as using something called Oracle Text. Can someone provide a quick example of how to do this?

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

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.

I needed to do this just recently and came up with the following solution (uses Spring JDBC)

String sql = "select * from clobtest where dbms_lob.instr(myclob, ? , 1, 1) > 0";
return (String) getSimpleJdbcTemplate().getJdbcOperations().queryForObject(sql, new RowMapper<Object>() {
  public String mapRow(ResultSet rs, int rowNum) throws SQLException {
    String clobText = lobHandler.getClobAsString(rs, "myclob");
    return clobText;
  }
}, searchText);

Seems to work pretty well, but I'm going to do some performance testing to see how well it works under load.