How to check any missing number from a series of numbers?

Question

I am doing a project creating an admission system for a college; the technologies are Java and Oracle.

In one of the tables, pre-generated serial numbers are stored. Later, against those serial numbers, the applicant's form data will be entered. My requirement is that when the entry process is completed I will have to generate a Lot wise report. If during feeding pre-generated serial numbers any sequence numbers went missing.

For example, say in a table, the sequence numbers are 7001, 7002, 7004, 7005, 7006, 7010. From the above series it is clear that from 7001 to 7010 the numbers missing are 7003, 7007, 7008 and 7009

Is there any DBMS function available in Oracle to find out these numbers or if any stored procedure may fulfill my purpose then please suggest an algorithm.

I can find some techniques in Java but for speed I want to find the solution in Oracle.

Answer 1

A solution without hardcoding the 9:

select min_a - 1 + level      from ( select min(a) min_a                  , max(a) max_a               from test1           )   connect by level <= max_a - min_a + 1     minus    select a      from test1 

---

Results:

MIN_A-1+LEVEL -------------          7003          7007          7008          7009  4 rows selected.