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

Question

I need to find the CLOB column, and I'm looking for a better way to do this. I saw options online using the DBMS_LOB package, as well as using something called Oracle Text. Can someone give a quick example of how to do this?

Answer 1

Oracle Text indexing is the way to go. You can use the CONTEXT or CTXRULE index. CONTEXT can be used for unstructured documents, where CTXRULE is more useful for structured documents.

This link will provide more information about index types and syntax.

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

You can also read posts on asktom.oracle.com

http://asktom.oracle.com/pls/apex/f?p=100:11:07::::P11_QUESTION_ID►533095920114

Answer 2

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

Oracle text is good if you are looking for words or phrases (this is probably what you have in the CLOB). Sometimes you store something “strange” in the CLOB, such as XML or a web service call return value, and it could be another fish maker.

Answer 3

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); 

Everything seems to be working fine, but I'm going to do some performance testing to see how well it works on boot.