How to search a string in entire database

Recently I came across an issue where there was a certain text being displayed in the application which was incorrect. This data was stored in the database, however we were unable to trace out which table/column had that entry. How I wish that I could just grep the whole oracle database for that string. Googled up everywhere for some ready made code to do that... But couldn't find any...Finally had to come up with my own pl/sql code...Here it goes..


set serveroutput on size 1000000
declare
TYPE QueryCurType is REF CURSOR;
query1 QueryCurType ;

cursor c1 is select owner,table_name from dba_tables where owner not in ('SYS','SYSTEM') and table_name not like '%$%';
cursor c2(t1 varchar2) is select column_name from dba_tab_columns where table_name=t1 and DATA_TYPE in ('NVARCHAR2','VARCHAR2','CHAR');
temp_var varchar2(3000);
query varchar2(3000);
begin
for tab1 in c1 loop
for col in c2(tab1.table_name) loop
query:='select '||col.column_name||' from '||tab1.owner||'.'||tab1.table_name||' where '||col.column_name||' like ''%Ciudad%definida%''';
--dbms_output.put_line('executing..'||query);
open query1 for query;
loop
fetch query1 into temp_var;
if concat('a',temp_var) != 'a' then
dbms_output.put_line('Found String: "'||temp_var||'"# Column:'||col.column_name||'# Table:'||tab1.table_name);
end if;
exit when query1%NOTFOUND;
end loop;
end loop;
end loop;
end;
/

The text in red is what we are searching for. Replace it with the text you would like to search.