Finding NULL in column

select column_name, num_nulls from all_tab_columns where table_name = 'SOME_TABLENAME' and owner = 'SOME_OWNER';

---------column contain Null------------------
set serveroutput on
declare
  l_count integer;
begin
  for col in (select table_name, column_name
              from user_tab_columns where table_name='&Enter_Table_Name')
  loop
    execute immediate 'select count(*) from '||col.table_name
                      ||' where '||col.column_name
                      ||' is null and rownum=1'
      into l_count;
    if l_count != 0 then
      dbms_output.put_line ('Column '||col.column_name||' contains nulls');
    end if;
  end loop;
end;
/


----column contain Only Null----------------------------------
set serveroutput on
declare
  l_count integer;
begin
  for col in (select table_name, column_name
              from user_tab_columns where table_name='&Enter_Table_Name')
  loop
    execute immediate 'select count(*) from '||col.table_name
                      ||' where '||col.column_name
                      ||' is not null and rownum=1'
      into l_count;
    if l_count = 0 then
      dbms_output.put_line ('Column '||col.column_name||' contains only nulls');
    end if;
  end loop;
end;
/

----------------------------------------------------------