Re: Sequence CURRVAL & NEXTVAL

"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 - 14:21:25 CDT