query each column of a table in a loop - Oracle Database

Question

I'm working with an oracle database and what I basically need to do is to count the number of NULL fields per column in a certain table.

something like that:

DECLARE
    BlankCount number(20);
    i number(2) := 1;

BEGIN
    loop that would take each column individualy and exit after the last one
        SELECT COUNT(*) INTO BlankCount FROM name_of_my_table
        DBMS_OUTPUT.PUT_LINE('Column '||i||' has '||BlankCount||' empty cells');
        i := i + 1;
    END LOOP;
END;

I just couldn't find anything that would do the loop part. It would also be nice if instead of just numbering them (with the i) I could display the column name (but that is not very important).

Thank you!

shonky linux user · Accepted Answer · 2012-07-25 03:37:26Z

5

Something like this:

declare 

  mytable varchar(32) := 'MY_TABLE';

  cursor s1 (mytable varchar2) is 
            select column_name 
            from user_tab_columns
            where table_name = mytable
            and nullable = 'Y';

  mycolumn varchar2(32);
  query_str varchar2(100);    
  mycount number;

begin

  open s1 (mytable);

  loop
     fetch s1 into mycolumn; 
         exit when s1%NOTFOUND;

     query_str := 'select count(*) from ' || mytable || ' where '  || mycolumn || ' is null';

     execute immediate query_str into mycount;

     dbms_output.put_line('Column ' || mycolumn || ' has ' || mycount || ' null values');

  end loop;
end;

answered Jul 25, 2012 at 3:37

shonky linux user

6,4965 gold badges50 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 12:10:34Z

0

Try using cursor approach and Dynamic SQL as mentioned in this thread: How to loop through columns in an oracle pl/sql cursor

HTH.

edited May 23, 2017 at 12:10

CommunityBot

11 silver badge

answered Jul 25, 2012 at 3:35

Thinhbk

2,2221 gold badge25 silver badges34 bronze badges

Collectives™ on Stack Overflow

query each column of a table in a loop - Oracle Database

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related