Oracle sequence nextval and currval confusing

  • A+
Category:Languages

In my procedure I write this

INSERT INTO questions(id, value) VALUES(my_seq.NEXTVAL, p_question);  INSERT INTO DEPENDENCIES(parent_question_id, child_question_id) VALUES (my_seq.CURRVAL, my_seq.NEXTVAL); 

Let's say sequence's last value equals to 1. In this case I expect this result:

 my_seq.NEXTVAL = 2;  my_seq.CURRVAL = 2;  my_seq.NEXTVAL = 3; 

But it inserts this:

 my_seq.NEXTVAL = 2;  my_seq.CURRVAL = 3;  my_seq.NEXTVAL = 3; 

I would like to know why does oracle retrieve sequence value in this way?

 


If you had three separate statements :

  1. nextval

  2. currval

  3. nextval

Then you would be correct. But you only have two

  1. nextval

  2. currval and nextval

Currval and nextval are part of a single atomic step - currval does not get processed before nextval.

You'll need to use variables for this :

DECLARE   l_parent_question_id     NUMBER ;   l_child_question_id      NUMBER ; BEGIN   l_parent_question_id := my_seq.NEXTVAL ;    INSERT INTO questions(id, value) VALUES(l_parent_question_id, p_question);    l_child_question_id := my_seq.NEXTVAL ;    INSERT INTO DEPENDENCIES(parent_question_id, child_question_id)   VALUES (l_parent_question_id, l_child_question_id);   END ; 

Comment

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: