在Oracle数据库中搜索Long数据类型的最佳方法是什么? [英] What is the best way to search the Long datatype within an Oracle database?

问题描述

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

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.

我尝试过，从表中选择*，其中'％form％'之类的列".这会导致以下Oracle错误，因为Long数据类型不支持"like".

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:数据类型不一致:预期的NUMBER个很长

ORA-00932: inconsistent datatypes: expected NUMBER got LONG

推荐答案

您可以在不使用临时表的情况下使用此示例:

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;

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

Of course there is a problem if LONG has more than 32K characters.

这篇关于在Oracle数据库中搜索Long数据类型的最佳方法是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！