Re: How to increased by exactly 1 ?

"Den" <dd_at_hotmail.net> wrote in message news:41f130cb$1_3_at_rain.i-cable.com...
> The system need to auto generate the Control Code column for the items.
> The
> business rule is that the system should have an option to assign the
> lowest
> next available Control Code for the new item created.

It seems to me that this business rule can be almot certainly be satisfied by sequences perfectly happily. There is nothing there at all about numbers being gap-free or sequential. The next available number from my sequence seq is the one returned by seq.nextval.

In addition, if you do interpret the rule literally and if you delete old items it is possible that the lowest next available control code is lower than the maximum current value of the control code column in which case you would have to compare every integer in the array {1...max(control_code)} with the current values for the control_code column and pick the lowest, picking max+1 won't cut it. I'm also a little worried by the word option in your business rule - it rather suggests that there is an 'option' to allocate control codes manually, if this is the case any auto-generation scheme may well collide with manual allocation.

If you do really, really wish to allocate the max+1 then you'll have to lock the table for the duration of the transaction select and use the number and then release the lock. This won't scale, which is why if you look at every vendors solution for this type of column they don't guarantee gap free values.

-- 
Niall Litchfield
Oracle DBA
http://www.niall.litchfield.dial.pipex.com