Oracle sequence nextval and currval confusing

Question

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?

Answer 1

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 ;