在Oracle数据库中搜索Long数据类型的最佳方法是什么？

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.

我正在使用Oracle数据库将HTML存储为Long数据类型。我想查询数据库以搜索存储在Long中的HTML数据中的特定字符串。

I tried, "select * from TABLE where COLUMN like '%form%'". This causes the following Oracle error because "like" is not supported for Long datatypes.

我试过，“从表中选择*，其中COLUMN喜欢'％form％'”。这会导致以下Oracle错误，因为Long数据类型不支持“like”。

ORA-00932: inconsistent datatypes: expected NUMBER got LONG

ORA-00932：不一致的数据类型：预期NUMBER变长

6 个解决方案

#1

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.

你无法直接搜索LONG。 LONG不能出现在WHERE子句中。它们可以出现在SELECT列表中，因此您可以使用它来缩小您必须检查的行数。

Oracle has recommended converting LONGs to CLOBs for at least the past 2 releases. There are fewer restrictions on CLOBs.

Oracle建议至少在过去的两个版本中将LONG转换为CLOB。对CLOB的限制较少。

#2

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.

当然，如果LONG的字符超过32K，则会出现问题。

#3

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.

不过要小心。 PL / SQL函数只搜索LONG的前32K。

#4

Don't use LONGs, use CLOB instead. You can index and search CLOBs like VARCHAR2.

不要使用LONG，而是使用CLOB。您可以索引和搜索像VARCHAR2这样的CLOB。

Additionally, querying with a leading wildcard(%) will ALWAYS result in a full-table-scan. Look into Oracle Text indexes instead.

此外，使用前导通配符（％）查询将始终导致全表扫描。请查看Oracle Text索引。

#5

First convert LONG type column to CLOB type then use LIKE condition, for example:

首先将LONG类型列转换为CLOB类型，然后使用LIKE条件，例如：

CREATE TABLE tbl_clob AS
   SELECT to_lob(long_col) lob_col FROM tbl_long;

SELECT * FROM tbl_clob WHERE lob_col LIKE '%form%';

#6

Best way is to convert your long column to clob as long is being deprecated. Or write a pl/sql block to read that data. See below link for clear explaination.

最好的方法是将长列转换为clob，因为弃用时间很长。或者编写一个pl / sql块来读取该数据。请参阅以下链接以获得清晰的解释。

References: http://www.oraclebin.com/2012/12/using-long-data-type-in-where-clause-in.html

参考文献：http：//www.oraclebin.com/2012/12/using-long-data-type-in-where-clause-in.html

#1