CODEWITHSUNDEEP
oracle-databaseplsql
aamir saif
aamir saif

Reputation: 13

query to find no of empty columns from a table along with the name of the column

I am getting an error when i am trying to use define command otherwise the code works fine please let me know how to use this code using define command. Define b_table='&Please enter the table name';

                    declare
                    v_count number;
                    cursor c2 is select Column_name 
                    from all_tab_columns
                     where table_name= '&b_table';

                    begin
                    for r1 in c2 loop
                    dbms_output.put_line(r1.column_name);
                    select count(r1.column_name) into v_count
                    from HR.'&b_table';
                    if v_count =0 then
                    dbms_output.put_line(v_count);
                    end if;
                    end loop;
                    end;
               old   5:  where table_name= '&b_table';
new   5:  where table_name= ' enter the table name';
old  11: from HR.'&b_table';
new  11: from HR.' enter the table name';
from HR.' enter the table name';
        *
ERROR at line 11:
ORA-06550: line 11, column 9:
PL/SQL: ORA-00903: invalid table name
ORA-06550: line 10, column 1:
PL/SQL: SQL Statement ignored

Upvotes: 0

Views: 43

Answers (1)

Nipun Alahakoon
Nipun Alahakoon

Reputation: 2862

try this

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

Upvotes: 1

Related Questions