Skip to Main Content
  • Questions
  • Why is output of select seqname.currval,seqname.nextval,seqname.currval,seqname.nextval from dual like this?

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Paul.

Asked: July 19, 2004 - 8:15 am UTC

Last updated: July 20, 2004 - 3:56 am UTC

Version: 8.1.7.0.0

Viewed 1000+ times

You Asked

Hi Tom,
I must say I'm a huge fan of urs. Its a great privilege to get an opportunity to post a question on this site. Getting down to business now.

At the sql prompt i give the following
I create a sequence:-

SQL> create sequence seq1 start with 1;
Sequence created.

I prime it for use
SQL> select seq1.nextval from dual;

NEXTVAL
---------
1
I execute the following statement
SQL> select seq1.currval,seq1.nextval,seq1.currval,seq1.nextval from dual;

CURRVAL NEXTVAL CURRVAL NEXTVAL
--------- --------- --------- ---------
2 2 2 2

Pardon my ignorance but I thought the output would be more like


CURRVAL NEXTVAL CURRVAL NEXTVAL
--------- --------- --------- ---------
1 2 1 2


Hope u can clear my doubt.

Thanx a lot

P.S. Ur doing a great job for the Oracle community. Keep up the good work.



and Tom said...

</code> http://docs.oracle.com/cd/B10501_01/server.920/a96540/sql_elements6a.htm#80564 <code>

documents this behavior and describes how sequences "work"

<quote>
If any of these locations contains references to both CURRVAL and NEXTVAL, then Oracle increments the sequence and returns the same value for both CURRVAL and NEXTVAL.
</quote>

Rating

  (1 rating)

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

Comments

Paul, July 20, 2004 - 3:56 am UTC

Thanx a lot for the quick reply. Ur simply the best.