Skip to Main Content
  • Questions

  • How to know the total number of rows contain null values in a table through script

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question.

Asked: June 19, 2012 - 7:20 am UTC

Last updated: June 22, 2012 - 7:11 am UTC

Version: 11.2.0

Viewed 10K+ times! This question is

You Asked

I have created a table
create table d_t (name varchar2(10), age number)

inserting data into it.

insert into d_t values('aaa',29)
insert into d_t values('bbb',39)
insert into d_t (name) values ('ccc')
insert into d_t (name) values ('ddd')
insert into d_t values ('eee',null)
insert into d_t (age) values (50)
insert into d_t values (null,60)

Now, i want to know how many rows contain null values for each columns of this table by creating the below scripts.

DECLARE
V_COUNT NUMBER;
BEGIN
FOR I IN
(SELECT ATC.COLUMN_NAME FROM ALL_TAB_COLUMNS ATC
WHERE TABLE_NAME = 'D_T' /*AND OWNER = 'CADWDAT'*/
AND NULLABLE = 'Y'
ORDER BY ATC.COLUMN_NAME)
LOOP
SELECT COUNT(*) INTO V_COUNT FROM D_T WHERE I.COLUMN_NAME IS NULL;
DBMS_OUTPUT.PUT_LINE (I.COLUMN_NAME || ' Contain Null Records ' || V_COUNT);
END LOOP;
END;

Each time I got every columns contain '0' rows for null values when I ran this query, which is not right data. Please help me to know where is the error in the above scripts. Its an urgent one. Thanks in advance.

and Tom said...


That is because the query:


SELECT COUNT(*) INTO V_COUNT FROM D_T WHERE I.COLUMN_NAME IS NULL;


is just like:

select count(*) into v_count from d_t where 'some string' is null;


I.COLUMN_NAME was bound into the query, it was the name of column and the name of a column is NEVER null.


Here is the query you might have been trying for:

ops$tkyte%ORA11GR2> select count(*) cnt, count(name) cnt_name, count(age) cnt_age
  2    from d_t;

       CNT   CNT_NAME    CNT_AGE
---------- ---------- ----------
         7          5          4

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select cnt-cnt_name null_names, cnt-cnt_age null_ages
  2    from (
  3  select count(*) cnt, count(name) cnt_name, count(age) cnt_age
  4    from d_t
  5         );

NULL_NAMES  NULL_AGES
---------- ----------
         2          3


one of those two. I like the first one myself, you might like the second - that would be up to you. And to make this "generic", you could:

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> create or replace procedure count_nulls( p_tname in varchar2, p_result in out sys_refcursor )
  2  as
  3      l_query varchar2(32000) := 'select count(*) cnt';
  4  begin
  5      for x in (select column_name
  6                  from user_tab_columns
  7                 where table_name = p_tname
  8                   and nullable = 'Y'
  9                 order by column_name )
 10      loop
 11          l_query := l_query || ', count("' || x.column_name || '") "'|| x.column_name || '"';
 12      end loop;
 13      l_query := l_query || ' from "' || dbms_assert.simple_sql_name( p_tname ) || '"';
 14      open p_result for l_query;
 15  end;
 16  /

Procedure created.

ops$tkyte%ORA11GR2> show errors
No errors.
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> variable c refcursor
ops$tkyte%ORA11GR2> exec count_nulls( 'D_T', :c );

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> print c

       CNT        AGE NAME
---------- ---------- ----
         7          4    5


if you want to make it do the other query - that is pretty simple:

ops$tkyte%ORA11GR2> create or replace procedure count_nulls( p_tname in varchar2, p_result in out sys_refcursor )
  2  as
  3      l_query varchar2(32000);
  4  begin
  5      for x in (select column_name
  6                  from user_tab_columns
  7                 where table_name = p_tname
  8                   and nullable = 'Y'
  9                 order by column_name )
 10      loop
 11          l_query := l_query || ', count(*)-count("' || x.column_name || '") "'|| x.column_name || '"';
 12      end loop;
 13      l_query := 'select ' || ltrim(l_query,',') || ' from "' || dbms_assert.simple_sql_name( p_tname ) || '"';
 14      open p_result for l_query;
 15  end;
 16  /

Procedure created.

ops$tkyte%ORA11GR2> show errors
No errors.
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> variable c refcursor
ops$tkyte%ORA11GR2> exec count_nulls( 'D_T', :c );

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> print c

       AGE NAME
---------- ----
         3    2

Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, June 19, 2012 - 11:06 am UTC

Hi can I write the programme in the following manner, kindly suggest.

DECLARE
V_COUNT NUMBER;
V_QUERY varchar2(1000);
BEGIN
FOR I IN
(SELECT COLUMN_NAME FROM ALL_TAB_COLUMNS ATC
WHERE TABLE_NAME = 'D_T' AND NULLABLE = 'Y' ORDER BY ATC.COLUMN_NAME)
LOOP
v_query := 'select /*+ parallel(a,4) */ count(*) from
D_T a where ' || I.COLUMN_NAME || ' is null';
execute immediate V_QUERY into V_COUNT;
DBMS_OUTPUT.PUT_LINE (I.COLUMN_NAME || ' Contain ' || '"' ||V_COUNT || '"' || ' Null Rows');
END LOOP;
END;
Tom Kyte
June 19, 2012 - 12:42 pm UTC

did you read my answer???????

seriously???


why would you want to full scan a table for each and every column, when you can do it easily in a single pass????????

I'm sticking with my answer - if you don't like it and want to do something else, that is fine.

Use a single pass.

A reader, June 21, 2012 - 4:29 am UTC 

when I write "variable c refcursor;" in the command promt in pl/sql developer it says: 
SQL> variable c refcursor;
REFCURSOR not supported

Kindly help in this regards.

Tom Kyte
June 21, 2012 - 6:13 pm UTC

what is pl/sql developer?


Pl/SQL Developer

A reader, June 21, 2012 - 10:43 pm UTC

Pl/SQL Developer is an IDE for Oracle ( http://www.allroundautomations.com/plsqldev.html ) similar to SQL Developer. It seems REFCURSOR is not supported in that tool.

SQL Developer or command line sqlplus support REFCURSOR variable.

Tom Kyte
June 22, 2012 - 7:11 am UTC

Please ask the people that make this product how to do it then??


Jonathan Taylor, June 22, 2012 - 12:20 pm UTC

in PL/SQL Developer, you can use the test window.
Right-click the procedure name in the object browser, and click test.

This will open a test window, with a snippet of code to run the procedure. Bind variables are defined at the bottom of the window.

The datatypes of most simple variables (number/varchar2) are worked out automatically - but you will need to set the type to "Cursor" as the type of the CURSOR bind variable.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library