Finding missing sequence in a table

Question

I'm using the Oracle 10g Database. i'm trying to figure out how to write a simple sql query to:

find the missing numbers in a table between say 86002895 and 86005197 (inclusive), There are 1955 rows between 86002895 and 86005197.

Ex: Current Scenario : table_1 :

tracking_no | id_value
86002895 | 10
86002896 | 10
86002899 | 10
86002900 | 10
86002910 | 10
86005196 | 10
86005197 | 10

Expected Result1:

" missing tracking_id " where id_value = 10 from table_1 ;

86002897

86002898
86002900 to

86002910

86002910 to

86005196

Thanks in advance

Answer 1

with data as 
(
  select tracking_no from table_1 where id_value = 10
),  
data_n as 
(
  select level + (select min(tracking_no) from data) n 
  from dual 
  connect by level <= (select max(tracking_no) - min(tracking_no) from data) 
)
select * from data_n
where not exists (select 1 from data where tracking_no = n);

If you want to include 86002895 and 86005197 do:

with data as 
(
  select tracking_no from table_1 
  where id_value = 10
  and   tracking_no between 86002895 and 86005197
),  
data_n as 
(
  select level + (select min(tracking_no) from data) n 
  from dual 
  connect by level <= (select max(tracking_no) - min(tracking_no) from data) 
)
select * from data_n
where not exists (select 1 from data where tracking_no = n);