在整个oracle数据库中搜索部分字符串 [英] search entire oracle database for part of string

问题描述

我正在尝试在整个Oracle数据库中找到特定的字符串.

I'm trying to find a specific string in an entire Oracle database.

我已在此处的另一个主题中关注了该示例(

I've followed the example in another topic on here (Search All Fields In All Tables For A Specific Value (Oracle)), and it's working when the string is the whole value in a column. But I need to search for the string as part of the column.

例如，如果我搜索"Alert"，则应返回所有带有"Alert"的列和所有带有"Alert_QB"的列

For example, if i search for 'Alert' it should return all columns with 'Alert' in and all columns with 'Alert_QB'

此为当前查询:

DECLARE
  match_count INTEGER;
BEGIN
  FOR t IN (SELECT owner, table_name, column_name
              FROM all_tab_columns
              WHERE data_type LIKE '%CHAR%') LOOP

    EXECUTE IMMEDIATE
      'SELECT COUNT(*) FROM ' || t.owner || '.' || t.table_name ||
      ' WHERE '||t.column_name||' = :1'
      INTO match_count
      USING 'ALERT';

    EXCEPTION when others then
    null;
    end;

    IF match_count > 0 THEN
      dbms_output.put_line( t.table_name ||' '||t.column_name||' '||match_count );
    END IF;

  END LOOP;

END;
/

我认为它靠近使用'ALERT';"行，我需要添加一些东西，但是我不知道是什么.

I think it's near the "USING 'ALERT';" line that I need to add something but I don't know what.

谢谢

推荐答案

将其更改为

EXECUTE IMMEDIATE
  'SELECT COUNT(*) FROM ' || t.owner || '.' || t.table_name ||
  ' WHERE '||t.column_name||' like :1'
  INTO match_count
  USING '%ALERT%';

这篇关于在整个oracle数据库中搜索部分字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！