Skip to Main Content
  • Questions

  • Understand the behaviour of nextval of a sequence in SQL and PL/SQL

Breadcrumb

XMAS_TREE

The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments. Please have a happy and safe festive season and as always, thanks for being a member of the AskTOM community.

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:
https://livesql.oracle.com/apex/livesql/s/ems24uk7vo9iz713ee62ho4kd

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)

We're not taking comments currently, so please try again later if you want to add a comment.

Comments

Many Thanks

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

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

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.