The following function will find any text matching the variable set within the procedure.
(the output will display on screen -dbms_output)
To execute: <SQL> [sql]execute update_found_records_proc;[/sql]
We could also add a parameter on calling the procedure as:
CREATE OR REPLACE PROCEDURE FIND_IN_RECORDS_PROC(val IN varchar2) IS…
note: ‘val’ is set once and used anywhere later in the code
Credit:: Rene Nyffenneger on Oracle
PL/SQL code:
[sql]CREATE OR REPLACE PROCEDURE "UPDATE_FOUND_RECORDS_PROC" 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); val varchar2(100); --the value to be found BEGIN begin val := 'ABC_553'; 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); 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; close cur; commit; --return 'Done searching.'; end; EXCEPTION WHEN OTHERS THEN RAISE_APPLICATION_ERROR(-20001, 'Error encountered - ' || SQLCODE || ' -ERROR- ' || SQLERRM); END UPDATE_FOUND_RECORDS_PROC; [/sql]
Comments