create
or
replace
procedure
visit_table(p_var
varchar
)
as
p_table user_all_tables.table_name % type;
cursor p_data is ( select t.table_name from user_all_tables t);
type p_col is ref cursor ;
p_c1 p_col;
type p_a is ref cursor ;
p_aa p_a;
p_name varchar2 ( 1000 );
p_type varchar2 ( 100 );
p_sql varchar2 ( 1000 );
p_result number ;
begin
open p_data;
loop
fetch p_data into p_table;
exit when p_data % notfound;
p_sql : = ' select * from ' || p_table;
open p_c1 for ' select t.COLUMN_NAME,t.DATA_TYPE from user_tab_columns t where t.TABLE_NAME =:1 '
using p_table;
loop
fetch p_c1 into p_name,p_type;
exit when p_c1 % notfound;
if (p_type <> ' BLOB ' ) then
p_sql : = ' select count(*) a from ' || p_table || ' where to_char( ' || p_name || ' )=:1 ' ;
-- dbms_output.put_line(p_sql);
execute immediate p_sql into p_result using p_var;
if (p_result > 0 ) then
dbms_output.put_line( ' table: ' || p_table || ' columns: ' || p_name);
end if ;
-- returning into p_result;
end if ;
end loop;
close p_c1;
end loop;
close p_data;
end ;
p_table user_all_tables.table_name % type;
cursor p_data is ( select t.table_name from user_all_tables t);
type p_col is ref cursor ;
p_c1 p_col;
type p_a is ref cursor ;
p_aa p_a;
p_name varchar2 ( 1000 );
p_type varchar2 ( 100 );
p_sql varchar2 ( 1000 );
p_result number ;
begin
open p_data;
loop
fetch p_data into p_table;
exit when p_data % notfound;
p_sql : = ' select * from ' || p_table;
open p_c1 for ' select t.COLUMN_NAME,t.DATA_TYPE from user_tab_columns t where t.TABLE_NAME =:1 '
using p_table;
loop
fetch p_c1 into p_name,p_type;
exit when p_c1 % notfound;
if (p_type <> ' BLOB ' ) then
p_sql : = ' select count(*) a from ' || p_table || ' where to_char( ' || p_name || ' )=:1 ' ;
-- dbms_output.put_line(p_sql);
execute immediate p_sql into p_result using p_var;
if (p_result > 0 ) then
dbms_output.put_line( ' table: ' || p_table || ' columns: ' || p_name);
end if ;
-- returning into p_result;
end if ;
end loop;
close p_c1;
end loop;
close p_data;
end ;
dd