ORACLE PL/SQL QUERY TO SEARCH FOR A PARTICULAR VALUE IN ALL THE DATABASE TABLES AND COLUMNS

SET SERVEROUTPUT ON;

DECLARE
    match_count   INTEGER;

BEGIN
    FOR i IN (SELECT table_name, column_name
                FROM all_tab_columns
               WHERE table_name LIKE 'XX%')
    LOOP
        BEGIN

            EXECUTE IMMEDIATE
                   'SELECT COUNT(*) FROM '
                || i.table_name
                || ' WHERE TO_CHAR('
                || i.column_name
                || ') LIKE :1'
                INTO match_count
                USING 'ENTER VALUE TO BE SEARCHED HERE';
        EXCEPTION
            WHEN OTHERS

            THEN
                DBMS_OUTPUT.put_line (i.table_name || '~' || i.column_name);
        END;


        IF match_count > 0
        THEN
            DBMS_OUTPUT.put_line (
                i.table_name || ' ' || i.column_name || ' ' || match_count);
        END IF;
    END LOOP;
END;
/