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

sql oracle sql-like ora-00932
Mike Munroe picture Mike Munroe · Apr 20, 2009 · Viewed 99.8k times · Source

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

Martin Selecký picture Martin Selecký · Dec 9, 2014

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.

Related questions

Case insensitive searching in Oracle

The default behaviour of LIKE and the other comparison operators, = etc is case-sensitive. Is it possible make them case-insensitive?

Read More
Using LIKE in an Oracle IN clause

I know I can write a query that will return all rows that contain any number of values in a given column, like so: Select * from tbl where my_col in (val1, val2, val3,... valn) but if val1, for example, …

Read More
SQL LIKE Performance with only the wildcard (%) as a value

I am wondering what the performance of a query would be like using the LIKE keyword and the wildcard as the value compared to having no where clause at all. Consider a where clause such as "WHERE a LIKE '%…

Read More