The procedure below helps in order to find a specific value in the tables declared at TABLES_SET cursor:
CREATE procedure OLTP_SIA.find_specific_value (theString IN VARCHAR2) is
match_count INTEGER;
the_table VARCHAR2(150);
CURSOR TABLES_SET IS
SELECT table_name,
column_name
FROM all_tab_columns
where 1=1
AND DATA_TYPE='VARCHAR2'
AND TABLE_NAME LIKE 'S%'
AND OWNER='OLTP_SIA';
BEGIN
FOR t IN TABLES_SET LOOP
EXECUTE IMMEDIATE
'SELECT COUNT(*) FROM '||t.table_name||' WHERE '||t.column_name||' = :1'
INTO match_count
USING theString;
the_table := t.table_name;
IF match_count > 0 THEN
dbms_output.put_line( t.table_name || '.' ||t.column_name||': '|| match_count || ' times' );
END IF;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line( the_table || ' has problem');
END;
/