search entire oracle database for part of string

Question

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

I'm not sure but that doesn't look quite right, as t.column_name should be outside the quotes shouldn't it? and the :1 in. So perhaps? where ||'t.column_name'|| like %:1%' But neither yours nor mine worked. I got the error saying "quoted string not properly terminated" with yours. — pietomb, Sep 09 '13 at 16:43
Moving it to dynamic code was left as an exercise; you need to quote everything differently but should be able to apply the concept. — Ben, Sep 09 '13 at 17:15

score 1 · Accepted Answer · answered Sep 09 '13 at 16:59

1

Change it to

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

answered Sep 09 '13 at 16:59

Chamal

1,439
10
15

percentage signs are provided with using "USING '%ALERT%';" – Chamal Sep 09 '13 at 17:17

score 0 · Answer 2 · answered Sep 09 '13 at 17:02

You can concatenate the bind variable with the wildcard % characters:

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

Note that the single quotes have to be escaped by doubling them up.

search entire oracle database for part of string

2 Answers2

Linked