Find Records Having Column with Specific Value

SET SERVEROUTPUT ON
DECLARE
CURSOR tab_cur is select * from user_tab_columns where column_name = 'COLUMN_NAME';
cnt NUMBER;
query_str VARCHAR2(1000);
BEGIN
for rec in tab_cur
loop
query_str := 'select count (*) from ' || rec.table_name || ' where COLUMN_NAME = ''&value''';
EXECUTE IMMEDIATE query_str into cnt;
IF cnt > 0 THEN DBMS_OUTPUT.PUT_LINE('There are ' || cnt || ' records in ' || rec.table_name);
END IF;
--DBMS_OUTPUT.PUT_LINE(query_str);
END LOOP;
END ;
/