Missing Numbers (Between two numbers)

[slimdave]

Originally posted by tamilselvan
The simple solution to find missing numbers is using cube:

I think that i would prefer to use either a list of numbers in a table or a pipelined function to generate the required values ...

create or replace type TypTabNum as table of number
/
create or replace package utilities
as
  function tab_of_numbers (p_start number, p_finish number) return TypTabNum pipelined;
end;
/

create or replace package body utilities
as
   function tab_of_numbers (p_start number, p_finish number)
      return TypTabNum
      pipelined
   is
   begin
      if p_finish > p_start
      then
         for i in p_start .. p_finish 
         loop
            pipe row (i);
         end loop;
      else
         for i in reverse p_finish .. p_start
         loop
            pipe row (i);
         end loop;
      end if;
   return;
   end;
end;
/

select * from table (utilities.tab_of_numbers(1,10))
/

select * from table (utilities.tab_of_numbers(10,1))
/

So anyway, you could ...

create table my_table
   (my_number number);
   
insert into my_table values (3);
insert into my_table values (4);
insert into my_table values (8);
insert into my_table values (10);
insert into my_table values (12);

commit;

select
   column_value as missing
from
   table (utilities.tab_of_numbers(
      (select min(my_number) from my_table),
      (select max(my_number) from my_table)))
minus
select
   my_number
from
   my_table
/

   MISSING
----------
         5
         6
         7
         9
        11

Just depends what kind of result set you want ... analytical functions are good for identifying ranges "5-7", this is good for listing individual values.