Find an unused number in a bunch of numbers

I have a huge table filled with numbers (1001001..1009999) and there are certainly a bunch of unused numbers in that area. So, I could have all these numbers returned by doing:

SELECT MY_IDENTIFIER FROM MY_TABLE

that would return all used numbers. How could I get a list of unused numbers from that table? I work serverside (rhino/jaxer) with JavaScript and my database is Oracle 10开发者_StackOverflow中文版g.

Thanks,

The following query will give you what you need:

SELECT seq_num
  FROM (SELECT (lvl + &&v_from - 1) seq_num
          FROM (SELECT *
                  FROM (    SELECT LEVEL lvl
                              FROM DUAL
                        CONNECT BY LEVEL <= (&&v_to - &&v_from) + 1)))
 WHERE seq_num NOT IN (SELECT my_identifier FROM my_table);

Replace &&v_to and &&v_from with your boundary numbers.

Reference: http://oraqa.com/2006/01/20/how-to-generate-sequence-numbers-between-two-numbers/

Javascript is not really related to that, isn't it? I did something similiar years before.

I had a table with all numbers in a range and joined these two tables and selected those numbers which could not be joined. A negative select.

Something like

 select nr bulk collect into nrs1 from tbl1 where nr not in
                (select nr from tbl2);