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
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.
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.
You can use this example without using temp table:
DECLARE
l_var VARCHAR2(32767); -- max length
BEGIN
FOR rec IN (SELECT ID, LONG_COLUMN FROM TABLE_WITH_LONG_COLUMN) LOOP
l_var := rec.LONG_COLUMN;
IF l_var LIKE '%350%' THEN -- is there '350' string?
dbms_output.put_line('ID:' || rec.ID || ' COLUMN:' || rec.LONG_COLUMN);
END IF;
END LOOP;
END;
Of course there is a problem if LONG has more than 32K characters.
First convert LONG type column to CLOB type then use LIKE condition, for example:
CREATE TABLE tbl_clob AS
SELECT to_lob(long_col) lob_col FROM tbl_long;
SELECT * FROM tbl_clob WHERE lob_col LIKE '%form%';
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 Oracle Text indexes instead.
Source: Stackoverflow.com