oRCLE HOW TO SERACH A TEXT IN ALL TABLES AND ALL COLUMN

SET SERVEROUTPUT ON SIZE 100000

DECLARE match_count INTEGER;

    v_owner all_tab_cols.owner%type  := 'NAUCRM';

    v_data_type all_tab_cols.data_type%type :='VARCHAR2';

    v_search_string VARCHAR2(4000) := 'Himanshu';

BEGIN

    FOR t IN (SELECT a.table_name, a.column_name

        FROM all_tab_cols a inner join total_tables_cba_COUNT_total b on a.table_name=b.Table_name

        WHERE  data_type in ('VARCHAR2') 

        and record_count<112000  and record_count>1) 

        LOOP

        EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM '||t.table_name

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

       INTO match_count

       USING 'Tri-cycle Fleet';

       IF match_count > 0 THEN

       insert into SEARCH_TABLE values(t.table_name,t.column_name);

       END IF;

    END LOOP;

END;

select distinct table_name from SEARCH_TABLE

delete from SEARCH_TABLE