create or replace procedure column_count
is
    --------------------------
    -- first create the table
    -- drop table TAB_COL_COUNT;
    -- create table TAB_COL_COUNT ( schema varchar2(32), table_name varchar2(32), column_name varchar2(32),column_count number(11),column_null varchar2(1),total_count number(11),null_count number(11));
    ------------------------
    
    -- get all Tables of the user
    cursor c_all_tables 
     is select table_name from user_tables
     -- if you need only some tables
     -- where table_name in ('TT')
     ;
    
    -- get all columns with possible null values and not long or raw
    cursor c_all_cols(p_tab_name varchar2)
     is select column_name,data_type from user_tab_cols 
         where table_name=p_tab_name 
           and data_type not in ('LONG','RAW') 
           and nullable='Y'
           ;

    v_count number(11);
    v_null_count number(11);
    v_total_count number(11);
    -- Normal query template
    v_count_template varchar2(512):='select count(werte),sum(null_count) from ( select ##COL_NAME## as werte ,sum(decode(##COL_NAME##,null,1,0)) as null_count from ##TABNAME##  group by ##COL_NAME##,decode(##COL_NAME##,null,1,0))';
    -- SQL Template to handle CLOB/BLOB we only use the first 10 char to check
    v_count_template_lob varchar2(512):='select count(1),sum(null_count) from ( select count(1),UTL_RAW.CAST_TO_VARCHAR2(dbms_lob.substr(##COL_NAME##,1,10)) ,sum(decode(UTL_RAW.CAST_TO_VARCHAR2(dbms_lob.substr(##COL_NAME##,1,10)),null,1,0)) as null_count from ##TABNAME##  group by UTL_RAW.CAST_TO_VARCHAR2(dbms_lob.substr(##COL_NAME##,1,10)),decode(UTL_RAW.CAST_TO_VARCHAR2(dbms_lob.substr(##COL_NAME##,1,10)),null,1,0)) ';
    -- Total Count
    v_total_count_template varchar2(512):='select count(1) from ##TABNAME##';
    v_sql varchar2(512);
    
    -- timing variables
    v_start_time TIMESTAMP:=CURRENT_TIMESTAMP;
    v_time_dif INTERVAL DAY TO SECOND;
    v_Seconds NUMBER ;
begin

   dbms_output.put_line('-- ----- Start --------------- ');
   
   --clear result table for each run
   execute immediate 'TRUNCATE TABLE TAB_COL_COUNT';
   
   -- Loop over all tables of the user with this procdure
   <<TAB_LOOP>>
   for rec in c_all_tables
    loop
     
        v_sql:=replace(v_total_count_template,'##TABNAME##',rec.table_name);
        execute immediate v_sql into v_total_count;
        dbms_output.put_line('--  Info :: Count table :: '|| rec.table_name ||' Total count ::'||to_char(v_total_count));
        <<COL_LOOP>>
        for colrec in c_all_cols(p_tab_name => rec.table_name)
        loop
       
            v_count:=0;
            v_null_count:=0;
        
            -- use right sql template to handle binary
            if colrec.data_type not in ('CLOB','BLOB') then
                v_sql:=replace(replace(v_count_template,'##TABNAME##',rec.table_name),'##COL_NAME##',colrec.column_name);
            else
                v_sql:=replace(replace(v_count_template_lob,'##TABNAME##',rec.table_name),'##COL_NAME##',colrec.column_name);
            end if;
        
            begin
                -- only if values in the table
                if v_total_count > 0 then
                    execute immediate v_sql into v_count,v_null_count;
                end if;
                -- remeber results
                insert into TAB_COL_COUNT ( schema, table_name,column_name,column_count, column_null,total_count,null_count)
                values ( user, rec.table_name,colrec.column_name, v_count,decode(v_count,'0','Y','N'),v_total_count,v_null_count);       
            exception 
                when others then
                dbms_output.put_line('--  Error ::  '||SQLERRM);
                dbms_output.put_line('--  Error ::  execute SQL '||v_sql);
                -- remeber the column but set values t0 -1 to identifiy trouble columns
                insert into TAB_COL_COUNT ( schema, table_name,column_name,column_count, column_null,total_count,null_count)
                values
                ( user, rec.table_name,colrec.column_name, -1,'X',v_total_count,-1);       
            end;    
               
       end loop COL_LOOP;    
    
    end loop TAB_LOOP;
    
    commit;
    
    -- get the timinig
    v_time_dif:=CURRENT_TIMESTAMP-v_start_time;
   
    SELECT EXTRACT( DAY    FROM v_time_dif ) * 86400
       + EXTRACT( HOUR   FROM v_time_dif ) *  3600
       + EXTRACT( MINUTE FROM v_time_dif ) *    60
       + EXTRACT( SECOND FROM v_time_dif )
    INTO
        v_Seconds 
    FROM DUAL ;
 
    dbms_output.put_line('-- ----- Finsh --------------- ');
    dbms_output.put_line('--  Info  ::  the run need '|| to_char(v_Seconds,'999G990D999'));
end;
/