Re: How to generate unique document number ?

krislioe_at_gmail.com schreef:

> Hi All,
>
> We need to generate Document Number where there should be any gap, so
> that we cannot use sequence. For example : Invoice Number.
>

Why not? Do not use the cache on a sequence. It will minimise the risk of "loosing" numbers due to shutdown. However, there's no always the possibility of a user that decides to rollback the transaction, or an error may occur, causing the transaction to be rolled back.

> one possible approach like : Select MAX(InvoiceNumber)+1 from invoice,
> what if that table has millions of rows, is this reliable enough ?

Reliable - in what way? There's not one way you would be able to ensure you will not be missing numbers, AND have a scalable solution.

> How to avoid two user do the same query at same time that lead to
> dupplicate number ?
>

By locking the table - as I said: no way it's going to scale. By creating an autonomous transaction - still possible to get missing numbers.

Bottom line: use a sequence, and explain some numbers may be missing - write to a "missing" table as soon as the number gets issued, and remove when the transaction completes. Make the write to your "missing" table an autonomous transaction Received on Wed Sep 13 2006 - 02:44:16 CDT