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?
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;
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;
