create table dist_val ( name varchar2(30) not null, 
                        colname varchar2(30) not null, 
                        val     varchar2(1000) not null,
                        cnt     number      not null,
                        primary key(name, colname, val))
organization index compress 2
tablespace USERS
overflow tablespace USERS ; 


declare 
    --null_v      constant varchar2(6):= '<NULL>';
    st          varchar2(4000);
    l_errcnt    int := 0;
begin
    for tab in (select table_name, column_name 
                from user_tab_columns 
                where table_name in (select table_name from user_tables
                                        where table_name != 'DIST_VAL')
                        and data_type in ('NUMBER', 'CHAR', 'DATE', 'VARCHAR2'
                         --   , 'TIMESTAMP(6)', 'TIMESTAMP(9)' 
                         )
                --    and rownum <=1
                order by table_name, column_name
                )
    loop
        begin
            st := ' insert into DIST_VAL select '''|| tab.table_name || ''', ''' || tab.column_name 
                ||  ''', substr(nvl(to_char(' || tab.column_name || '),''<NULL>''), 1, 1000) t, count(*) cnt from ' 
                || tab.table_name || ' group by ' || tab.column_name;
            --dbms_output.put_line(st);
            execute immediate st;  
        exception    
        when others then
            l_errcnt := l_errcnt + 1;
            dbms_output.put_line(sqlerrm || ' on ' || st || ' ' || tab.table_name || '.' || tab.column_name || ' is skipped ' );
        end;
    end loop;     
    commit;     
    dbms_output.put_line('done with ' || l_errcnt || ' errors.');       
exception
    when others then 
        rollback;
        raise;            
end;
/