Re: duplicate sequence numbers

In article <95vnak$vni$1_at_nnrp1.deja.com>,   Mike Krolewski <mkrolewski_at_rii.com> wrote:
> In article <95vlc1$tu4$1_at_nnrp1.deja.com>,
> dileepr_at_my-deja.com wrote:
> > I have been using a d2k developed package for over a year without
> > problem but recently I am unable to save transactions where a
 sequence
> > generated "nextval from dual" number has to be inserted into a
 table.
> > Message I get is the number is not unique. I am surprised because
> > oracle is supposed to generate unique sequential numbers from any
> > sequence. Or am i wrong?
> >
> > Can somebody please give me the solution?
> >
> > The funny part is that some inserts go through but most(about 80%)
 fail
> > and give a duplicate number message.
> >
> >
>
> The sequence number is unique to any user of that number.
>
> However, you can reset the sequence number to new starting value. Or
> the insert of the sequence does not have to be done -- another piece
 of
> code could be setting the number.
>
> First check that the sequence is in fact greater than the largest
 value
> in that column. If not reset it to some number above that number ( +
> 5 ).
>
> If the sequence is not the problem, you need to review the code
> associated with inserting this record. I recall some code where the
> code grabbed a sequence number using the cache. The code internally
> incremented the sequence, then only when it thought it was out
> requested the next set. The sequence cache was changed and the code of
> course broke.
>
> --
> Michael Krolewski
> Rosetta Inpharmatics
> mkrolewski_at_rii.com
> Usual disclaimers
>

Everything Mike said is true, but here is something else to consider:

How many digits is the database column defined to hold?

What is the maximun number value the sequence was set to, and if it is not unlimited was it set to cycle?

If the sequence was set to 5 digits and to cycle then once if hit 99999 and was invoked again it would start over at 1. This is not an uncommon situation and will work fine as long as the original data has been deleted before the sequence value is reused. If some of the original rows were not deleted then you get problems similar to what you described.

--
Mark D. Powell  -- The only advice that counts is the advice that
 you follow so follow your own advice --


Sent via Deja.com
http://www.deja.com/