Query Returning value as 0

Question

I am trying to execute following PL/SQL script in SQL Developer. The loop should return count of nulls but somehow everytime it is returning 0. set serveroutput on

DECLARE

--v_count number;
v_count_null number;

BEGIN
execute immediate 'select count(*) from SP_MOSAIX' into v_count;

FOR  i in (select column_name from all_tab_COLUMNS where table_name = 'SP_MOSAIX')
LOOP
select count(*) into v_count_null from SP_MOSAIX  where i.column_name IS NULL ;
dbms_output.put_line(v_count_null);

END LOOP;

END;

So when I run this, following output is what i get:

0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

But if I manually execute the query subsituting column_name I get the result.

select count(*) into v_count_null from SP_MOSAIX  where i.column_name IS NULL; 

Can anybody help on this?

Answer 1

There are a couple of things going on here that you need to be aware of. Firstly, as you allude to, your COUNT query is executing using the value of i.column_name, which is never NULL.

Secondly, COUNT(*) returns the number of rows that match your WHERE clause condition, regardless of NULL values. If you want to count how many NOT NULL values there are in a particular column, you must COUNT values in that column explicitly.

Please see the following example (SQL Fiddle):

Oracle 11g R2 Schema Setup:

CREATE TABLE null_col_vals (
  col_without_nulls INTEGER NOT NULL
, col_with_nulls INTEGER
, col_with_mix INTEGER  
)
/

INSERT INTO null_col_vals (col_without_nulls, col_with_nulls, col_with_mix)
VALUES (1, NULL, NULL)
/

INSERT INTO null_col_vals (col_without_nulls, col_with_nulls, col_with_mix)
VALUES (1, NULL, 1)
/

INSERT INTO null_col_vals (col_without_nulls, col_with_nulls, col_with_mix)
VALUES (1, NULL, NULL)
/

Query 1:

SELECT 
  COUNT(col_without_nulls) col_without_nulls
, COUNT(col_with_nulls) col_with_nulls
, COUNT(col_with_mix) col_with_mix
, COUNT(*) all_rows
FROM null_col_vals

Results:

| COL_WITHOUT_NULLS | COL_WITH_NULLS | COL_WITH_MIX | ALL_ROWS |
----------------------------------------------------------------
|                 3 |              0 |            1 |        3 |

As you can see, COUNT(*) always returns the number of rows present, but the others vary in results depending on whether NULL values are present in the specified column or not.

You'll need to use EXECUTE IMMEDIATE to convert your column name into part of the query. Something like this might do the job:

Query 2:

DECLARE

  l_count INTEGER;

BEGIN

  FOR r_col IN (
    SELECT *
    FROM all_tab_columns atc
    WHERE atc.table_name = 'NULL_COL_VALS'
  )
  LOOP

    dbms_output.put_line(r_col.column_name);

    EXECUTE IMMEDIATE 'SELECT COUNT(' || r_col.column_name || ') FROM null_col_vals' 
    INTO l_count;

    dbms_output.put_line(l_count);

  END LOOP;

END;

Answer 2

You need to use dynamic SQL, which will allow you to reference each column:

FOR  i in (select column_name from all_tab_COLUMNS where table_name = 'SP_MOSAIX')
LOOP
    execute immediate 'select count(*) from SP_MOSAIX  where '||i.column_name||' IS NULL'
     into v_count_null ;
    dbms_output.put_line(i.column_name||' = '||v_count_null);

END LOOP;