Re: Sequence numbers jumping

It appears that you have cache=20 on your sequence generator. Any time the oracle instance is restarted you lose up to 20 numbers.

If your are not bouncing oracle then the problem is in your code. We put the sequence generation in the pre-insert triggers in the database or in the form. This prevents wasting of numbers when the user starts a new record and then deletes the record before they commit it.

Michael Krolewski (mkrolews_at_upw.com) wrote:
: Fenella Tan wrote:
: >
: > We're having problems with our sequence numbers. For some reason, they
: > seem to be jumping up by 20 instead of the specified increment by 1.
: > Any ideas on what may cause this ? We're using Oracle 7.3 on UNIX with
: > a Visual Basic front-end. The jumps happen intermittently. It'll work
: > (increment by 1) for a few records, and then it'll suddenly go up by 20
: > again. Appreciate any input.
: >
: > Fenella
 

: This may be a problem of trys and retrys. Every attempt to create a
: record will increment the sequence number. However, obviously if
: no record is created, the sequence number is used!!
 

: One could try using a on-insert trigger. It means that the record is
: about to be written (the VB frontend has successfully communicated
: with the database), and therefore the sequence is used only once.
 

: A second possibility is that the VB front-end is creating a record
: which is then immediately discarded. I would presume that the
: action to create (commit) a record is done after the user has entered
: all the required data and the data has been validated.
 

: Mike Krolewski

--
This posting represents the personal opinions of the author. It is not the
official opinion or policy of the author's employer. Warranty expired when you
opened this article and I will not be responsible for its contents or use.