hint oracle qbname,在整个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.

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

This is the query at the moment:

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;

/

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

Thanks

解决方案

Change it to

EXECUTE IMMEDIATE

'SELECT COUNT(*) FROM ' || t.owner || '.' || t.table_name ||

' WHERE '||t.column_name||' like :1'

INTO match_count

USING '%ALERT%';