Question:

I have written this query for finding the total number of column which does not have a value in a table but its not working.

SET serveroutput ON; DECLARE v_count NUMBER; v_table_name VARCHAR2(200); CURSOR c2 IS SELECT Column_name FROM all_tab_columns WHERE table_name= A; BEGIN FOR r1 IN c2 LOOP dbms_output.put_line(r1.column_name); EXECUTE immediate('SELECT COUNT(r1.column_name) INTO v_count FROM HR'||'.'||A); IF v_count =0 THEN dbms_output.put_line(v_count); END IF; END LOOP; END;

I would like you to try this code. It works for me :)

SET serveroutput ON; DECLARE myOwner VARCHAR2(20):='HR'; myTable VARCHAR2(25):='A'; CURSOR c2 IS SELECT column_name, avg_col_len FROM all_tab_columns WHERE table_name = myTable AND owner = myOwner; BEGIN FOR r1 IN c2 LOOP IF r1.AVG_COL_LEN =0 THEN dbms_output.put_line('column_name = '||r1.column_name); END IF; END LOOP; END;

Hope this can help you.