CODEWITHSUNDEEP
sqloracle-databaseplsql
pietomb
pietomb

Reputation: 27

search entire oracle database for part of string

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

Upvotes: 0

Views: 3884

Answers (2)

Alex Poole
Alex Poole

Reputation: 191275

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.

Upvotes: 0

Chamal
Chamal

Reputation: 1449

Change it to

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

Upvotes: 1

Related Questions