Get oracle sequenve next value without change it

Question

I have a big problem. I need to know the next value of an oracle sequence without changing it.

If I use sequence.NEXTVAL , I get what I need. The problem is that the value changed, so the next record will get this value + 1 and it's not good for me.

I know I can use sequence.CURRVAL and it's great because it does not change the value, but in case of no records, it's not working and in this case the sequence value can be any number (and not only 1 cause the sequence value steel exist).

Please help me to find a solution.

Thank you vary much !!!!!

Answer 1

You should definitely not rely on never missing a value in a sequence, as they optimise for concurrency over sequential numbering. There are quite a few situations in which a number can be "lost".

Furthermore, the value visible in the dba_sequences may not be the actual next value, as the numbers are assigned from an in-memory cache. The underlying sequence metadata table has no data on the usage of that cache. You should also bear in mind that in a RAC system each instance has its own cache of sequence numbers.

You might describe the problem you are trying to solve, as it could be that sequences are not an appropriate mechanism for you.

Answer 2

In multi-user environment there is no way. The value is either known - when you call .NEXTVAL or unknown. Sequences can not be locked.

Answer 3

Well the only suggestion that I can give you is to get the nextval and then revert back. But CAUTION: Perform these operations only while you sequence is not begin used from any other session. Otherwise if sequence is fetched from another session during this operation, it is possible that you could never revert back to the original value. So be cautious before checking:

In my database, current value of seq_test is 6

--Get the next value

SQL> SELECT seq_test.NEXTVAL FROM dual;

   NEXTVAL
----------
         7

-- Ok, so this is the next value, now Restore to Original

SQL> ALTER SEQUENCE seq_test INCREMENT BY -1;

Sequence altered.

SQL> SELECT seq_test.NEXTVAL FROM dual;

   NEXTVAL
----------
         6

-- Restore original increment by clause

SQL> ALTER SEQUENCE seq_test INCREMENT BY 1;

Sequence altered.

--Check

SQL> SELECT seq_test.CURRVAL FROM dual;

   CURRVAL
----------
         6

I again warn you that this could be fatal ;-)

Answer 4

Look at the "last_number" column from

select * from USER_SEQUENCES

Edit:

This works only if the sequence is created with the nocache option.

So if the sequence does has a cache setting, drop it and recreate it with nocache, and then you can use user_sequences.last_number +1.

Note that if the sequence is heavily used than this does impact performance. If on the other hand, the sequence is not used so much, it will be fine.

Short of writing the application (which is the best solution), this is only solution.

Answer 5

I can't see any reason why you want to know the next value of a sequence. The only importance for the value is when you have used it for a new record. So you either create a new record and return the ID value immediately:

declare
  l_id number;
begin
   Insert into table (id) 
   values (sequence.nextval) 
   return id into l_id;

   -- and then do something more with the ID value
end;

or first get a new ID and use it afterwards

declare   
   l_id number; 
begin
   select sequence.nextval    
   into l_id    
   from dual;

    -- then do something with the ID value 
end;