row count of columns with NUMBER datatype only using dynamic sql in oracle -

i did count of rows every table in schema. need row count of columns in tables have datatype number. don't know how row count of individual number datatype columns of every table ?

declare   t_c1_tname user_tables.table_name%type;   t_command  varchar2(200);   row_count integer;   cursor c1 select table_name user_tables order table_name; begin    open c1;   loop         fetch c1 t_c1_tname;         exit when c1%notfound;         t_command  := 'select count(*) '||t_c1_tname ;         execute immediate t_command  row_count;         dbms_output.put_line('table : '|| t_c1_tname || 'count : '|| row`enter code here`_count);   end loop;   close c1; end; 

this want, think:

set serveroutput on declare   not_null_count integer;   null_count integer; begin   r in (     select utc.table_name, utc.column_name     user_tab_columns utc     data_type = 'number'     order utc.table_name, utc.column_id   )   loop     execute immediate 'select count("' || r.column_name || '"),       count(nvl2("' || r.column_name || '", null, 1))       "' || r.table_name || '"'     not_null_count, null_count;     dbms_output.put_line('table: ' || r.table_name       || ' column: ' || r.column_name       || ' not-null: ' || not_null_count       || ' null: ' || null_count);   end loop; end; / 

if create data:

create table t42(num1 number, num2 number, str1 varchar2(5));  insert t42 values (1, 2, 'a'); insert t42 values (3, 4, 'b'); insert t42 values (5, null, 'c'); 

then block shows:

table: t42 column: num1 not-null: 3 null: 0 table: t42 column: num2 not-null: 2 null: 1 

---

if you're querying all_tab_columns instead, need , use table owner:

  r in (     select atc.owner, atc.table_name, atc.column_name     all_tab_columns atc     data_type = 'number'     order atc.owner, atc.table_name, atc.column_id   )   loop     execute immediate 'select count("' || r.column_name || '"),       count(nvl2("' || r.column_name || '", null, 1))       '"' || r.owner || '"."' || r.table_name || '"'     not_null_count, null_count;