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