0

Hello DBA,

Can any one help me this?
The table TEST contains

CNAME CNO EXTRA
------------------------------ ---------- ----------
bijaya 645896
mantri 525896
mantri12 525126
testing for null

My requirement is to find the column which contains all the rows null(null column)

I have created the following procedure to accomplish this
create or replace procedure test_null
is
cursor c1 is select column_name from user_tab_columns where table_name = 'TEST';
cnt number := 0;
begin


for i in c1
loop
select count(i.column_name) into cnt from test;

dbms_output.put_line(i.column_name || ' contains ' || cnt);
if cnt = 0 then
insert into test_temp values(i.column_name);
end if;
cnt:=0;
end loop;
end;
/

This give the result
CNAME contains 4
CNO contains 4
EXTRA contains 4

if i run the SELECT statment in sql plus then
SQL> select count(extra) from test;

COUNT(EXTRA)
------------
0
i am not able to find the difference.
please help me to short out this problem

Thanks,
BMantri

3
Contributors
5
Replies
6
Views
9 Years
Discussion Span
Last Post by bmantri
0

yes,
it is showing me 4 records. Actually, these rows are filled with nulls.

1

Think you need to replace your select from

select count(i.column_name) into cnt from test;

to

execute immediate 'select count(1) from test where ' || i.column_name || ' is not null'
into cnt;

This is needed because you can't dynamically pick which column you want to include in a SQL statement by using binding ( as far as I know of anyway ). This replaced statement should count just rows where that column isn't null.

Nige

0

Hello Nige,
Thanks a lot for providing me this valuable solution..
Gr8 yaar, it is working fine now.

Many Many thanks and merry christmas to You.
BMantri

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.