Re: How to avoid sequence from skipping numbers

Oracle sequences are not subject to transactions. The NXTVAL is not subject to rollback nor commit. It is updated permanently upon access.

Instead of using Oracle sequences, you can update a table. This update WOULD be subject to transaction control and could be rolled back. However, concurrent transactions would be serialized (waiting for a commit). So, there is a good reason for Oracle sequences: it allows concurrent transactions to use unique numbers if you don't care about gaps. If you cannot stand gaps, update a table and lose some concurrancy.

S S Wan wrote:
>
> 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 (??!!).
>
> Regards,
>
> S S Wan
>
> P.S. Please remove the .NOSPAM in my email address when reply by mail.
Received on Sun Apr 26 1998 - 23:13:33 CDT