Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How To Find All Table/Columns Where Some Data Is

BladeRunnerApr 24 2009 — edited Apr 26 2009
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;
/
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on May 24 2009
Added on Apr 24 2009
4 comments
727 views