Back numbers in Oracle sequences
570420Dec 30 2007 — edited Jan 8 2008We are using a J2EE based tool that uses Oracle as backend. For several purposes the tool use the oracle sequences. As per general rule the every time the sequence is referred, it generates a next higher number and returns the same for any kind of storage purposes like primary key, etc. But in rare cases we have got a previous number of the sequence instead of a next higher value. We aren't sure if that previous number was ever used before or not. But the problem is how does the oracle sequence return a previous number? e.g. we have a sequence running as 220, 221, 222, etc. then suddenly instead of returning 223 the next time, it may give the number 214 (or basically any previous number).
Does anybody know why does that happen? What could be the possible reasons and how it can be avoided? It creates confusion amongst users and does not seem just right to get a previous number. Any help is appreciated. Thanks.