/*
  Autor: Edgar Valdez
  Goal: Determinate which columns are null in a specific schema-table
  Parameters: You need to change the value of variable: cEsquema and cTabla, writing the schema and table name in that order
*/
set serveroutput on;
declare nRegistrosTabla int;
        nRegistrosNull int;
        cTabla  varchar2(50);
        cSQL    varchar2(1000);
        cEsquema  varchar2(50);
        cColumnName varchar2(100);
        TYPE cCursor IS REF CURSOR;
        cColumnas cCursor;
        
BEGIN
  cEsquema := 'schema_name';
  cTabla := 'table_name';
  
  cSQL := 'SELECT COUNT(*) FROM ' || cEsquema || '.' || cTabla;

  BEGIN
    EXECUTE IMMEDIATE cSQL INTO nRegistrosTabla;
    EXCEPTION 
      WHEN OTHERS THEN
      dbms_output.put_line(SQLERRM);  
      dbms_output.put_line(SQLCODE);  
  END;
  
 cSQL := 'SELECT column_name
          FROM all_col_comments
          WHERE owner = ' || '''' || cEsquema || '''' ||
          ' AND TABLE_NAME = ' || '''' || cTabla || '''';  
  OPEN cColumnas FOR cSQL;   
  LOOP
    FETCH cColumnas INTO cColumnname;
    EXIT WHEN cColumnas%NOTFOUND;
    cSQL := 'SELECT COUNT(*) FROM ' || cTabla || ' WHERE ' ||  cColumnname  || ' IS NULL ';  
     BEGIN
      EXECUTE IMMEDIATE cSQL INTO nRegistrosNull;
      EXCEPTION 
        WHEN OTHERS THEN
          dbms_output.put_line(SQLERRM);  
          dbms_output.put_line(SQLCODE);  
    END;
  
    IF nRegistrosTabla = nRegistrosNULL THEN
      dbms_output.put_line(cTabla || '.' || cColumnname);
    END IF;
  END LOOP;
  CLOSE cColumnas;
 
END;