(OFFTOPIC) Simplest method for searching a LONG column using SQL IN Oracle Database 10G

In this example we will see how we can search a LONG column using SQL.

The simple concepts used in the example are LONG column can be converted to CLOB using TO_LOB function ,CLOB column is searchable and rowid is used to uniquely identify a row in a table.

First create a Table with values as the rowid and LONG column of the table in which you want to search the LONG column.

Create table test_long_search as select rowid row_id, to_lob(message) message  from message_log;

Here Message_Log is the table which contains the LONG column you want to search.

Then you can search the value as

select * from test_long_search where message like '%FAILED FOR SOME REASON%'

you will get

ROW_ID	MESSAGE
AAAswsAAKAAAK8OAAA	<CLOB>
AAAswsAAKAAAK/kAAC	<CLOB>

Then in the main table you can search like

select * from message_log where rowid in ('AAAswsAAKAAAK8OAAA','AAAswsAAKAAAK/kAAC');

EMAILED	REFERENCE_NO	RECEIVER	MESSAGE
Y	123456	SAN	<Long>
Y	123456	SAN	<Long>

we hope this example was helpfull :)