What is the best way to search the Long datatype within an Oracle database?

Question

I am working with an Oracle database that stores HTML as a Long datatype. I would like to query the database to search for a specific string within the HTML data stored in the Long.

I tried, "select * from TABLE where COLUMN like '%form%'". This causes the following Oracle error because "like" is not supported for Long datatypes.

ORA-00932: inconsistent datatypes: expected NUMBER got LONG

Answer 1

You can't search LONGs directly. LONGs can't appear in the WHERE clause. They can appear in the SELECT list though so you can use that to narrow down the number of rows you'd have to examine.

Oracle has recommended converting LONGs to CLOBs for at least the past 2 releases. There are fewer restrictions on CLOBs.

Answer 2

Don't use LONGs, use CLOB instead. You can index and search CLOBs like VARCHAR2.

Additionally, querying with a leading wildcard(%) will ALWAYS result in a full-table-scan. Look into http://download.oracle.com/docs/cd/B19306_01/text.102/b14218/toc.htm>Oracle Text indexes instead.

Answer 3

Example:

create table longtable(id number,text long);

insert into longtable values(1,'hello world');
insert into longtable values(2,'say hello!');

commit;

create or replace function search_long(r rowid) return varchar2 is
temporary_varchar varchar2(4000);
begin
select text into temporary_varchar from longtable where rowid=r;
return temporary_varchar;
end;
/


SQL> select text from longtable where search_long(rowid) like '%hello%';                                                                              

TEXT
--------------------------------------------------------------------------------
hello world
say hello!

But be careful. A PL/SQL function will only search the first 32K of LONG.