select statement in a procedure

[bmantri]

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

[debasisdas]

Even though there is no record in the EXTRA column still then it contains 4 rows.

[bmantri]

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

[debasisdas]

But still then the rows are there .

[Nige Ridd]

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

[bmantri]

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