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: Sybrand Bakker <gooiditweg_at_sybrandb.verwijderdit.demon.nl>
Date: Thu, 12 Aug 2004 00:13:40 +0200
Message-ID: <jc6lh0997lc53okabk84p5gsge7vd8q53v@4ax.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?
>
>

There isn't. You also have to use nextval before you can use currval at all. And wasting sequencenumbers is not a problem, or you shouldn't be using sequences.

--
Sybrand Bakker, Senior Oracle DBA
Received on Wed Aug 11 2004 - 17:13:40 CDT

Original text of this message

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