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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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 gt  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

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

Examples related to sql

Examples related to oracle

Examples related to sql-like

Examples related to ora-00932