Re: How to get available IDs?

> Hi, I think this is a beginner's question: I want to get all available
> employee numbers by sql to put new employees into the system. If emp_no
> is NUMBER(3) then this is how I do it:
>
> SELECT rownum - 1 FROM very_big_table WHERE rownum <= 1000
> MINUS
> SELECT emp_no FROM emp
>
> The problem is that I need that very_big_table to do the trick. If I
> want to to the same for article numbers NUMBER(6) for example, that
> very_big_table had to be even bigger!
>
> Is there another simple way I just haven't thought of? I've thought of
> MIN and MAX from emp, but they don't help as 0 and 999 might be used,
> whereas the other numbers aren't. I have thought about sequences also,
> but a sequence doesn't know about which numbers are still in use, so it
> doesn't help either. Any ideas?

Rownum is absolutely out. Rownum is assigned by Oracle on-the-fly and depending upon the query any row can have any number. If MIN() and MAX() are out it is pretty obvious that you have a flawed design and are fighting Oracle rather than working with it.

Here's what you should do.

1. Late on Friday night or over the weekend when the system is not being used renumber everyone beginning with the number 1.
2. Create a sequence, an Oracle database object, from which all subsequent numbers are generated. Start the sequence with the first number above the last one assigned.
3. In the future obtain all numbers during your insert statement with SQL such as the following:

INSERT INTO emp
(emp_no)
VALUES
(next_emp_no_sequence.nextval);

Daniel A. Morgan Received on Tue Jan 16 2001 - 23:48:24 CST