Sequence CURRVAL & NEXTVAL
From: CV <cv_at_nospamadelphia.net>
Date: Wed, 11 Aug 2004 18:01:39 -0400
Message-ID: <ZvCdnRxJuOj0CofcRVn-qQ_at_adelphia.com>
[Quoted] [Quoted] We have a procedure that tries to insert records into a table and uses the [Quoted] [Quoted] ID generated by a sequence as the primary key. Some inserts result in [Quoted] exceptions due to a Unique_key constraint. This is a desired behaviour for some exception handling that we do.
[Quoted] However, using the inserts as described above results in "wasting" many of [Quoted] the IDs from the sequence because the numbers generating an exception are [Quoted] 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 [Quoted] the very first insertion & I get the following error: ORA-08002: sequence TEST_SEQ.CURRVAL is not yet defined in this session
Date: Wed, 11 Aug 2004 18:01:39 -0400
Message-ID: <ZvCdnRxJuOj0CofcRVn-qQ_at_adelphia.com>
[Quoted] [Quoted] We have a procedure that tries to insert records into a table and uses the [Quoted] [Quoted] ID generated by a sequence as the primary key. Some inserts result in [Quoted] exceptions due to a Unique_key constraint. This is a desired behaviour for some exception handling that we do.
[Quoted] However, using the inserts as described above results in "wasting" many of [Quoted] the IDs from the sequence because the numbers generating an exception are [Quoted] 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 [Quoted] 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 Thu Aug 12 2004 - 00:01:39 CEST