LONG fields were used in Oracle DB during the Dark Ages to store long text, but they are not selectable and cannot be used in where clauses with LIKE. A multitude of broken solutions exist (to_lob,substr,weird macros, converting the value into new tables, etc...) online to be able to search into this field with a normal query with LIKE, but they are mostly broken. This sample declares a temporary function for the conversion (size up to 4k, it's an oracle limit, some sei you can go up to 32k in PL/SQL but this does not seem the case) and then uses it in a query, ugly but simple.
Errors you could see while handling LONG fields:
ORA-00997: illegal use of LONG datatype
ORA-06502: PL/SQL: numeric or value error
How to do a simple select from where LONG_FIELD like '%something%' in pure PL/SQL in one go:
with function convertLong(r rowid)
return varchar2 is
tmp varchar2(4000)
begin
select LONG_FIELD into tmp from TABLE_WITH_LONG_FIELD where rowid=r;
return tmp;
exception
when VALUE_ERROR then
return '';
end;
select t.ID, t.DESC, t.LONG_FIELD
from TABLE_WITH_LONG_FIELD t
where convertLong(t.rowid) like '%find this text%';Enjoy.