The following function will find any text matching the arguments given at command line.
(output may not display on screen, but data will be inserted in the relevant table)
To execute: [sql]select find_in_schema(‘ABC_553’) from dual;[/sql]
Credit:: Rene Nyffenneger on Oracle
PL/SQL code:
[sql]create or replace function find_in_schema(val varchar2) return varchar2 is v_old_table user_tab_columns.table_name%type; v_where Varchar2(4000); v_first_col boolean := true; type ref_cur is ref cursor; cur ref_cur; v_rowid varchar2(20); begin for r in (select t.* from user_tab_cols t, user_all_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); --dbms_output.put_line('select rowid from "' || v_old_table || '" ' || v_where); open cur for 'select rowid from "' || v_old_table || '" ' || v_where; fetch cur into v_rowid; loop exit when cur% notfound; dbms_output.put_line(' rowid: ' || v_rowid || ' in ' || v_old_table); --TODO --insert into records_found(rowid_number,table_name) -- values(v_rowid,v_old_table); fetch cur into v_rowid; end loop; v_old_table := r.table_name; end if; if v_first_col then v_where := ' where ' || r.column_name || ' = ''' || val || ''''; v_first_col := false; else v_where := v_where || ' or ' || r.column_name || ' = ''' || val || ''''; end if; end loop; return 'Done searching.'; end; [/sql]
Comments