How To Find All Table/Columns Where Some Data Is
I'm trying to create a pl/sql anonymous block that will take some string of data and the name of a schema and using the all_tab_columns table find all the table/column where the data resides. This is conceptually simple enough but my pl/sql syntax is a little rusty. Here is what I have so far, what am I doing wrong:
declare
cursor c_table_col (
schema in all_tab_columns.owner%TYPE) is
select table_name, column_name
from all_tab_columns
where owner = schema ;
output varchar2(1024);
finddata varchar2(64);
dataowner varchar2(64);
begin
finddata := '22657';
dataowner := 'AGILE';
for curr_data in c_table_col(dataowner) loop
select curr_data.column_name
from curr_data.table_name
where curr_data.column_name like finddata;
output := finddata || ' Col: ' || curr_data.column_name || ' Table: ' || curr_data.table_name;
sys.dbms_output.put_line(output);
end loop;
end;
/