CODEWITHSUNDEEP
sqloracle-databasesql-likeora-00932
Mike Munroe
Mike Munroe

Reputation:

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

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

Upvotes: 15

Views: 125222

Answers (5)

nikli
nikli

Reputation: 2369

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%';

Upvotes: 5

Martin Selecký
Martin Selecký

Reputation: 141

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.

Upvotes: 14

Neil Kodner
Neil Kodner

Reputation: 2956

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.

Upvotes: 3

Thomas Aregger
Thomas Aregger

Reputation: 540

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.

Upvotes: 8

Shea
Shea

Reputation: 11243

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.

Upvotes: 12

Related Questions