Re: How to avoid sequence from skipping numbers

>It is possible that a transaction within which a sequence is selected
>finally roll back. In this case the sequence is not rolled back however and
>the number will be skipped forever.
>
>Is there any method to avoid this? I want a sequence which will never skip
>numbers for generation of things like voucher number which is a business
>requirement (??!!).

S S Wan,
Once the nextval is selected off the sequence, this value is no longer available for another to insert directly from the sequence. To meet your business requirement try this scenario: Assume :
table A must have all primary key "ivalue" with values in incremental sequence.
Action:
Create a table B with column "tvalue" which is defined with the same data type and constraints as A.ivalue . The values placed in "tvalue" are either the most recently stored value of "ivalue" in table A or the last value selected from the sequence.
If B.tvalue > A.ivalue then
insert into A.ivalue should be B.tvalue Else
insert into B.tvalue the nextval from the sequence, insert into A.ivalue the B.tvalue.
If the insert into A fails or is rolled back then table B has the next value in the sequence to be used.
I would recommend writing a stored procedure or trigger with this logic. Cheers,
Nevin Hahn
nevin.hahn_at_born.com Received on Sun Apr 26 1998 - 23:46:17 CDT