Re: Sequence CURRVAL & NEXTVAL

CV -

Why the fretting over the "wasting" of sequence numbers? A sequence will guarantee
that unique values will be generated from it, and no guarantee that it will be
gap-free. As long as it's unique, then your requirements have been met, right?

Tom Kyte, a highly respected fellow in these here parts, has talked extensively about sequences and why it's impossible (and therefore not practical at all) to maintain a gap-free sequence. He talks about the effects that rollbacks, multiple users, etc. have on sequences at his excellent site AskTom (http://asktom.oracle.com/pls/ask/f?p=4950:1:)

Specifically, take a look at some of the things that Tom has written:
http://asktom.oracle.com/pls/ask/f?p=4950:8:18189290334961000588::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:530735152441,
http://asktom.oracle.com/pls/ask/f?p=4950:8:11452056973460007700::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:2159521121323,
http://asktom.oracle.com/pls/ask/f?p=4950:8:11452056973460007700::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:1508205334476,

and many others...

CV wrote:

>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?
>
>
>
>
>
Received on Wed Aug 11 2004 - 17:34:47 CDT