15

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

Aldwoni
  • 1,168
  • 10
  • 24

5 Answers5

13

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.

Martin Selecký
  • 131
  • 1
  • 3
11

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.

Shea
  • 11,085
  • 2
  • 19
  • 21
  • 11
    Yet, a describe on ALL_TRIGGERS shows the TRIGGER_BODY as a long. Recommend and then don't do it themselves? odd... – xQbert Jan 15 '12 at 19:45
  • Same with DBA_IND_EXPRESSIONS. If you describe it, COLUMN_EXPRESSION is still a long type in 11GR2. – pahariayogi Mar 03 '15 at 16:33
  • I wish Oracle would fix the dictionary LONG columns too, but given the size and complexity of the data dictionary, I suspect upgrading all LONGs to CLOB would be a huge exercise that would risk corruption during upgrades and so Oracle has so far decided to leave it alone. – William Robertson Feb 20 '18 at 10:14
  • If you are looking for the text of the trigger, you have the option of using DBA_SOURCE, USER_SOURCE, or ALL_SOURCE instead. – ScrappyDev Sep 07 '18 at 13:39
8

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.

Thomas Aregger
  • 520
  • 3
  • 8
  • If you want to delete the matching rows, see this answer: http://stackoverflow.com/questions/2381203/oracle-delete-where-long-like/2381600#2381600 – Sam Mar 05 '10 at 10:13
  • what does rowid=r means. I mean here "select text into temporary_varchar from longtable where rowid=r;" – AlphaBetaGamma Dec 18 '14 at 15:03
5

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%';
nikli
  • 2,281
  • 2
  • 24
  • 38
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 Oracle Text indexes instead.

Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
Neil Kodner
  • 2,901
  • 3
  • 27
  • 36