Re: sequence numbers

art wrote:

> I have a problem with generating a 'next sequence number'. I have a table
> (products) with n rows of configuration information. One column in the table
> is 'nsn' (next sequence number) which is the next sequence value to be
> generated for that product. This is a number which a process selects (via
> SELECT), uses and then updates (vai UPDATE) with nsn+1. There is a NSN for
> each product e.g.
>
> Product NSN
> -------- -----
> Prod1 1
> Prod2 37
> Prod3 1
>
> The fact that a process does a select/use/update on the NSN means that if
> two processes are processing the same product then they can both SELECT
> simultaneously and have the same NSN (which would be wrong and very bad for
> the system). I want NSN to remain sequential per product, and so cannot use
> an individual sequence for all products, and do not want a spearate sequence
> for each product as users can add and remove products arbitrarily.
>
> I want a stored procedure with the following prototype which guarantees that
> two processes sharing a database cannot establish the same NSN, and the NSN
> remains sequential on a per product basis.
>
> number GetNextSequenceForProduct(product_number)
> {
> -- Atomically get and update the NSN for product_number
> -- return established NSN
> }
>
> I am very confused about how to do this in a process safe manner. Any
> guidance would be much appreciated.
>
> Cheers,
> .

Have you actually tried this or are you speculating? It is impossible for two different anythings to get the same number from a sequence.

If you can prove otherwise ... report the bug to Oracle.

Daniel Morgan Received on Mon May 20 2002 - 16:58:14 CDT