how to find out missing number from seqentail numbers?

Question

I got table1 that contains sequential number in range between 1 - 100 and I want to get out missing number using pl without creating new table

I tried to do it by this way but it absolutely not professional way and I tried to use select min_val.nextval keyword but I fail so any simple and professional way to do it

declare 
min_val number(4);
max_val number(4);
current_val number(4);


cursor ids_cur is 
select ids from test1;

begin 

for ids_rec in ids_cur
loop
if min_val is not null then 
min_val:=min_val+1;
DBMS_OUTPUT.PUT_LINE(min_val);
else
DBMS_OUTPUT.PUT_LINE(min_val);

end if;
end loop;
end;

I read taht post b4 posting I am looking for more optimized way and I dont want to create table for my sequnce — user334560415, Feb 16 '14 at 12:56
Optimised for what? The fastest way speed wise is a pre-created table, and for a mere 100 values with no apparent plan to change the range, the optimal solution in other respects, ie simplest. — Tony Hopkinson, Feb 16 '14 at 13:07
there are around 2 million records im not ganna create a table with 2 million IDS records list just to check sequential numbers and they are divide into 36 type I separate between them using regular expression and for each type there are different way to catch that sequential number and each type has it owns limit like type1 has limit of sequential number between 1 to 9999 so I think there is another way to do that — user334560415, Feb 16 '14 at 13:48
You said 0, 100, I a programmer, not a clairvoyant... A table with th sequence definitions would give you a chance at a non-recursive CTE to create a range and then do an outer join where id is null manoevre — Tony Hopkinson, Feb 16 '14 at 14:27

score 2 · Answer 1 · answered Feb 16 '14 at 14:00

2

The most efficient way to do such a query is to use a select statement rather than a cursor. Set-based operations are faster. Here is one method, using a recursive CTE:

with nums(n) as (
      select 1 as n from dual
      union all
      select n + 1
      from nums
      where n < 100
     )
select nums.n as MissingId
from nums left outer join
     test1
     on nums.n = test1.id
where test1.id is null;

This should generalize pretty easily to 9,999 values.

If you just want to know if numbers are missing, there are other approaches for that.

answered Feb 16 '14 at 14:00

Gordon Linoff

1,242,037
58
646
786

thats will give me an error coz there is no into statement since i want to do it inside an pl script am I right ? – user334560415 Feb 16 '14 at 16:35
@user3304876 . . . You can set a cursor over this expression and read out the values one-by-one. That is ok -- better than doing the logic in PL/SQL. – Gordon Linoff Feb 16 '14 at 16:37

score 0 · Answer 2 · answered Feb 17 '14 at 10:55

I come up with this solution but i am gtting error message that fetch return more than one row but I am reading cursor from record whay ??!

declare 
current_num number(4);

cursor c1 is 
select ids from test1;

 BEGIN

 for rec in c1 
 loop
select rownum into current_num from  all_objects  
   where  rownum<=(select max(ids) from  test1)  
   minus
   select ids
   from test1;

   DBMS_OUTPUT.PUT_LINE(current_num);
 end loop;
 END;

how to find out missing number from seqentail numbers?

2 Answers2