Re: How to generate unique document number ?

krislioe_at_gmail.com wrote:
> Hi All,
>
> We need to generate Document Number where there should be any gap, so
> that we cannot use sequence. For example : Invoice Number.
>
> one possible approach like : Select MAX(InvoiceNumber)+1 from invoice,
> what if that table has millions of rows, is this reliable enough ?
> How to avoid two user do the same query at same time that lead to
> dupplicate number ?
>
> Anybody has done it this way in production? or any other approach ?
>
> Thank you for your info,
> xtanto

What you propose works right up until it doesn't. But even when it does work it doesn't scale.

There is no such thing as a method that will guarantee no gaps unless you can guarantee that no insert will ever fail and no server will ever crash.

I would suggest you use a sequence and capture in an exception handler failures and log them in a "numbers not used" table. The same can be done with an AFTER STARTUP event trigger. Check the next number in the sequence to the last number used and record any gaps caused by the shutdown.

-- 
Daniel Morgan
University of Washington
Puget Sound Oracle Users Group