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

Question

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

我尝试过"select * from TABLE where COLUMN like '%form%'"，但由于Long数据类型不支持"like"，因此会导致以下Oracle错误：

ORA-00932：不一致的数据类型：期望 NUMBER 类型，得到 LONG 类型

- Mike Munroe

Answer 1

您可以不使用临时表来使用此示例：

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;

如果LONG超过32K个字符，那么肯定会有问题。

- Martin Selecký

Answer 2

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

Oracle自过去两个版本以来一直建议将LONG转换为CLOB。 CLOB上有更少的限制。

- Shea

---

ALL_TRIGGERS表中描述TRIGGER_BODY的数据类型为long。建议却不自己实践？有些奇怪... - xQbert

与DBA_IND_EXPRESSIONS相同。如果您进行描述，COLUMN_EXPRESSION在11GR2中仍然是长类型。 - pahariayogi

我希望Oracle也能修复字典LONG列的问题，但考虑到数据字典的规模和复杂性，我怀疑将所有LONG升级为CLOB将是一个巨大的任务，在升级过程中可能会出现数据损坏的风险，因此Oracle迄今决定不去碰它。 - William Robertson

如果你正在寻找触发器的文本，你可以选择使用DBA_SOURCE、USER_SOURCE或ALL_SOURCE。 - ScrappyDev

Answer 3

例子：

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!

但要小心。PL/SQL函数仅会搜索LONG字段的前32K。

- Thomas Aregger

---

如果您想删除匹配的行，请参考此答案： http://stackoverflow.com/questions/2381203/oracle-delete-where-long-like/2381600#2381600 - Sam

"rowid=r" 是什么意思？我的意思是在这里 "select text into temporary_varchar from longtable where rowid=r;" - AlphaBetaGamma

Answer 4

首先将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%';

- nikli

Answer 5

不要使用LONG，改用CLOB。您可以像VARCHAR2一样对CLOB进行索引和搜索。

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

- Neil Kodner