Re: How to generate unique document number ?

From: Connor McDonald <connor_mcdonald_at_yahoo.com>
Date: Wed, 13 Sep 2006 21:12:21 +0800
Message-ID: <450803B5.4AC7@yahoo.com>

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

Depends on your definition of "no gaps", although both are going to cause you grief:

"no gaps at any time, sequential"

everyone must "select currval+1 for update", or equivalent update against a control table and hold that lock for the entirity of the relevant transaction...big time scaling problem there.

b) "no gaps over time"

you use a sequence, and autonomously store that in a table. The last part of your "real" transaction deletes that row as you commit. Thus over time, you'll have a smattering of rows left in this table. So what everyone must do is:
i) for i in ( select * from leftovers ) loop

         select for update this row nowait
         if no error, then use this sequence else try next row

ii) if no rows obtained in (i), then get next sequence value

You could fudge (i) using the undocumented SKIP LOCKED.

hth
connor

if you really mus

-- 
Connor McDonald
Co-author: "Mastering Oracle PL/SQL - Practical Solutions"
Co-author: "Oracle Insight - Tales of the OakTable"

web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: connor_mcdonald_at_yahoo.com


"Semper in excremento, sole profundum qui variat."

------------------------------------------------------------

Received on Wed Sep 13 2006 - 08:12:21 CDT