Thanks to all who replied. I am convinced that we can allow gaps in
sequences & that I should not look for a way around it to satisfy some inner
sense of order :)
"Yong Huang" <yong321_at_yahoo.com> wrote in message
news:b3cb12d6.0408121121.64a6b310_at_posting.google.com...
> "CV" <cv_at_nospamadelphia.net> wrote in message
news:<ZvCdnRxJuOj0CofcRVn-qQ_at_adelphia.com>...
> > We have a procedure that tries to insert records into a table and uses
the
> > ID generated by a sequence as the primary key. Some inserts result in
> > exceptions due to a Unique_key constraint. This is a desired behaviour
for
> > some exception handling that we do.
> > However, using the inserts as described above results in "wasting" many
of
> > the IDs from the sequence because the numbers generating an exception
are
> > not inserted. The inserts use sequence.NEXTVAL before doing an insert.
> > To maintain continuity in the ID values, I have tried using temporary
> > variables to hold the value of sequence.CURRVAL & increment the sequence
> > value only if the insert succeeds. However, this approach doesn't work
for
> > the very first insertion & I get the following error:
> > ORA-08002: sequence TEST_SEQ.CURRVAL is not yet defined in this session
> >
> > Is there a way of using CURRVAL & NEXTVAL in an if-loop, so that their
> > values are incremented only if a certain condition is met?
>
> As others say, if you need to absolutely guarantee no gaps on a number
> column, you shouldn't use Oracle sequences. It's almost impossible to
> do so. If the business requires it (as in the case of bank check
> numbers), you have to take the very unscalable approach and manually
> store, fetch and delete numbers in your own table.
>
> Having said that, there are ways to get the current value of a
> sequence without incrmenting the value. Take a look at
> http://rootshell.be/~yong321/computer/sequence.txt and let me know any
> error. One more way is simply manipulating the last number by giving a
> negative increment after you get its nextval: alter sequence myseq
> increment by -1.
>
> Yong Huang
>
> Yong Huang
Received on Thu Aug 12 2004 - 15:30:10 CDT
