create or replace procedure test(ownername in varchar2,tablename in varchar2) is
type vref is ref cursor;
cur_col_cnt vref;
col_name varchar2(50);
sqltext varchar2(200);
colname varchar2(50);
cnt varchar2(50);
cursor cur_colname is
select COLUMN_NAME from all_tab_columns where owner=ownername AND TABLE_NAME=tablename;
begin
open cur_colname;
loop
fetch cur_colname into col_name;
exit when cur_colname%notfound;
sqltext:=’select ‘||col_name||’, count(1) from ‘||ownername||’.’||tablename||’ group by ‘||col_name;
dbms_output.put_line(sqltext);
open cur_col_cnt for sqltext;
loop
fetch cur_col_cnt into colname,cnt ;
exit when cur_col_cnt%notfound;
dbms_output.put_line(colname||’:’||cnt);
end loop;
close cur_col_cnt;
end loop;
close cur_colname;
end test;