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:01:39 CDT
