Find Value in database/schema

Question: How to find any value in any column in a schema?

I have been searching for this answer for long, and stumbled upon a beautiful response in Oracle Forums and tried the block and stunning to find the output much easily. This saved my day as I was searching for occurence of a value in entire schema. Thanks for pollywog for the posting.

I will reproduce the anonymous block here for my readers:

declare
aCount pls_integer;
begin
for c in
(select table_name, column_name 
from all_tab_columns 
where owner = 'OWNER' 
and data_type = 'DATATYPE') loop


execute immediate 'select count(*) from '||c.table_name||' where '||c.column_name||' = ''value_to_find'' ' into aCount;
if aCount > 0 then
dbms_output.put_line('found value_to_find in table '||c.table_name||', column '||c.column_name);
end if;
end loop;
end;

Just modify the following in the block:
1. OWNER (The schema in which find is required)
2. DATATYPE (Data type of value which you are searching)
3. value_to_find (The string you are finding)

The output is like:

found value_to_find in table , column

Hope this tip helps you too, leave back a comment in case you like this tip.