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 :
Then you would be correct. But you only have two
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 ;