Saturday 25 May 2019

Oracle Procedure to Search for a Particular String/Character

Oracle Procedure to Search for a Particular String/Character - Full Schema Scan


The below mentioned Oracle procedure will search for any particular string/character in all the tables for a particular schema and will list the count of number of rows for every column where that string/character is found. The code can be altered as per the need.

DECLARE
  match_count INTEGER;
  v_owner VARCHAR2(255) :='ENTER_SCHEMA_HERE';
  v_data_type VARCHAR2(255) :='VARCHAR2';
-- Type the string you are looking at
  v_search_string VARCHAR2(4000) := '@';

BEGIN
  FOR t IN (SELECT table_name, column_name FROM all_tab_cols where owner=v_owner and data_type = v_data_type) LOOP

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

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

  END LOOP;
END;
/

I hope this helps !!

No comments:

Post a Comment