0

I created a sequence with beginning value as 2.

create sequence seq_ben
start with 2
increment by 1
nocache
nocycle;

when i was asked to show the next two numbers of the sequence i wrote

select seq_ben.nextval from dual

and ran this code twice to give next two values, then i was asked to show the next sequence without triggering it to move the next number and Use its next three values to add new rows to the the above sequence. Is this possible ? how can it generate a next sequence without triggering it?

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
benny
  • 17
  • 1
  • 7
  • If "without triggering it" means "without calling `nextval` and causing the sequence to increment" you cannot, in general. You could probably query the `dba_sequences` view if you make a lot of assumptions that would be ridiculous in reality (`nocache` sequence, no other sessions active, single user system, etc.). I have no idea what "add new rows to the above sequence" would possibly mean. – Justin Cave Jul 31 '14 at 23:19
  • 1
    `currval` gets the current sequence number without incrementing it. – Gordon Linoff Jul 31 '14 at 23:24
  • 1
    Note that to use `currval` you need to have invoked `nextval` in the current session at least once. – Bob Jarvis - Слава Україні Aug 01 '14 at 00:44
  • possible duplicate of [How to retrieve the current value of an oracle sequence without increment it?](http://stackoverflow.com/questions/10210273/how-to-retrieve-the-current-value-of-an-oracle-sequence-without-increment-it) – sampathsris Aug 01 '14 at 02:51

1 Answers1

0

You can use CURRVAL, if you have referenced NEXTVAL at least once in the current session.

However, I believe that if you really want to know the next number in the sequence, there is something fundamentally wrong about your design. Sequences are design such that NEXTVAL is an atomic operation, and no two sessions may get the same number. Or an incrementing unique identifier, in other words. That's the only guarantee it gives you. With this design, it is almost meaningless to ask for the next possible value of a sequence.

You may try to use MAX(), which is often used as a poor man's solution to sequences.

sampathsris
  • 21,564
  • 12
  • 71
  • 98