Skip to Main Content
  • Questions
  • Understand the behaviour of nextval of a sequence in SQL and PL/SQL

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Giovanni.

Asked: February 26, 2017 - 3:16 pm UTC

Last updated: February 27, 2017 - 1:01 am UTC

Version: 12.2.0.1.0

Viewed 1000+ times

You Asked

Hello Oracle Masters,
I have a question about the nextval pseudocolumn of a sequence.
I thought that the call of nextval produces each time a new integer, while currval returns the current value of the sequence.

In the script that you see in the livesql-link,
this is not the case: unexpectedly the first two employees have id_1 = id_2.

Moreover I have searched in the official documentation and I found this sentence:
PL/SQL Language reference (E50727-04, page 6-4):
PL/SQL evaluates every occurrence of sequence_name.CURRVAL and sequence_name.NEXTVAL
(unlike SQL, which evaluates a sequence expression for every row in
which it appears).


This sentence is not clear to me.

My questions:
1) what is wrong in my script?
2) related to the PL/SQL Language Reference: can you explain or provide an example to show the difference, how SQL and PL/SQL treat nextval?

Many thanks in advance.

Kind regards
Giovanni


with LiveSQL Test Case:

and Connor said...

Thank you for using LiveSQL - makes our job of digesting the question much easier.

Your example is actually a perfect example of the documentation. (ie, once execution per row in SQL)

Notice in your SQL you did:

insert into emp_with_double_id values (my_seq.nextval, my_seq.nextval, 'First Employee');

so you referenced the sequence twice, but it only got evaluated once (ie, once per row). Hence you got the same value.

Later in your PL/SQL code, we evaluated (and incremented) the sequence every time you referenced it (a,b,c,d).

Hope this helps.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Many Thanks

Giovanni, February 27, 2017 - 8:31 pm UTC

Thanks a lot for your quick answer, that was very useful!