Skip to content
January 20, 2011 / doganay

SEARCH DATABASE FOR STRING

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,

To search a string through the entire database:

/* SEARCH_DB_FOR_STRING by Mustafa DOGANAY - 2011 */
create or replace function f_search_db_for_string(val varchar2)
return varchar2 is
v_old_table user_tab_columns.table_name%type;
v_where Varchar2(4000);
v_first_col boolean := true;
type rc is ref cursor;
c rc;
v_rowid varchar2(20);
begin
for r in (
select
t.*
from
dba_tab_cols t, dba_tables a
where t.table_name = a.table_name
and t.data_type like '%CHAR%'
order by t.table_name) loop

if v_old_table is null then
v_old_table := r.table_name;
end if;

if v_old_table <> r.table_name then
v_first_col := true;

dbms_output.put_line('searching ' || v_old_table);

open c for 'select rowid from "' || v_old_table || '" ' || v_where;

fetch c into v_rowid;
loop
exit when c%notfound;
dbms_output.put_line(' rowid: ' || v_rowid || ' in ' || v_old_table);
fetch c into v_rowid;
end loop;

v_old_table := r.table_name;
end if;

if v_first_col then
v_where := ' where ' || r.column_name || ' like ''%' || val || '%''';
v_first_col := false;
else
v_where := v_where || ' or ' || r.column_name || ' like ''%' || val || '%''';
end if;

end loop;
return 'Success';
end;
/


How to use:

select f_search_db_for_string('Data you are searching for..') from dual;
  • I appreciate adp-gmbh.ch:

http://www.adp-gmbh.ch

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: