find missing values

How to find missing values in a column of a table in oracle database

declare
vid number(12);
vmin number(12);
vmax number(12);
vcount number(12);
vi number(12);
v number(12);
cursor c is
select distinct id
from demo
order by id;
begin
open c;
select min(id) into vmin from demo;
select max(id) into vmax from demo;
vcount:=vmax-vmin;
for vi in 1..vcount loop
fetch c into vid;
select count(*) into v from demo where id=vi;
if v=0 then
--dbms_output.put_line(vi);
insert into demo1 values(vi);
end if;
end loop;
end;
/

----------------------------------------------------------------------------------------------------------
Note:
Here, demo is a table name and id is a column name. demo1 is a table name as well. You can display values in two different ways. first one is using dbms_output.put_line()  function and the another one is inserting in demo1 table.

No comments:

Post a Comment

Popular Posts