So your goal is to obtain "unused" numbers? It will be a slow, cumbersome and error-prone solution.
You should try really hard to avoid it. What's the reason behind the requirement?
If at all possible, you should assign the numbers from a sequence.
The next best approach might be to create a table of
available numbers. Picking an available number is then the same as selecting a row (any row) from this table and then deleting it. You'll need to make sure concurrent accesses won't result into two users getting the same number (but this is true for every solution except the sequence, for which this is guaranteed by the database). When the table gets empty, some procedure would populate it with the next batch of available numbers. If numbers can become available later (when a record is deleted from some table, for example), you could add the freed number to the table.
What you describe will require an "antijoin", and it is a relatively expensive DB operation (in relation to the previous solution(s), at least). If you don't want to use any other table, you'd have to generate a table of numbers first using some available Oracle technique, such as
select level from dual connect by level <= max_number query. Your final solution could look like this:
You need to know the maximum available number and use it in the query; numbers higher than thousands will probably lead to very bad performance. IF used heavily by many users, you might probably get into trouble much faster with much lower numbers. Moreover, if two users execute this statement in parallel, they'll probably get the same number. If that matters, you'd need to lock the table
random_numbers, for example, add the obtained number(s) into it and then commit. Don't you want to use the sequence instead?
