Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Sequence CURRVAL & NEXTVAL

Re: Sequence CURRVAL & NEXTVAL

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Thu, 12 Aug 2004 08:37:21 +1000
Message-ID: <opsclagjhu3d8uqx@shostakovich.dizwell.com>


On Wed, 11 Aug 2004 18:01:39 -0400, CV <cv_at_nospamadelphia.net> 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?

Oracle has never guaranteed gapless sequences. And therefore it's a design flaw to use them if gaps are a problem.

What's the problem, precisely, with "wasting" numbers anyway? It's not as if they are a scarce resource, after all.

Regards
HJR Received on Wed Aug 11 2004 - 17:37:21 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US