Retrieving CURRVAL and NEXTVAL in a single query

Question

Using SQLPlus, I have the following sequence generated:

CREATE SEQUENCE pubnum_seq
START WITH 7
    INCREMENT BY 2
MAXVALUE 1000;

What I want to do is write a single query that retrieves both the NEXTVAL and the CURRVAL in the sequence. It seems like it should be simple enough. I've tried the following statement:

SELECT pubnum_seq.currval, pubnum_seq.nextval
FROM dual;

However this retrieves the same value for both.

CURRVAL - 7
NEXTVAL - 7

If I try the statements separately, then it gives me what I want, but I'm stump as to how to do this in one query.

You can make your edit the answer to your question (and even accept that). — mabi, Nov 19 '14 at 11:03

score 3 · Answer 1 · answered Dec 19 '14 at 22:34

3

Well, after searching through the docs at Oracle, I'm guessing that my statement is correct after all. According to this docs page:

If a statement contains references to both CURRVAL and NEXTVAL, Oracle increments the sequence and returns the same value for both CURRVAL and NEXTVAL regardless of their order within the statement.

Doesn't quite make sense to me for Oracle to do that, but I'll take their word for it.

answered Dec 19 '14 at 22:34

JasonMArcher

14,195
22
56
52

I moved the "answer" to an Answer post and out of the question. – JasonMArcher Dec 19 '14 at 22:34
Wow this is poor design by Oracle. Just ran across this, but in my case, I need `CurrVal` after `NextVal` so it's not a problem for me. But still.. poor design. – Jimenemex May 08 '18 at 19:50

score 0 · Answer 2 · answered Nov 19 '14 at 10:53

I had the same problem. After a lot of research we made two separated queries.

First one checking the current value and second generating a new one.

Seems that the database notices the 'nextval' in the SELECT statement and immediately replaces the 'old' current value.

Retrieving CURRVAL and NEXTVAL in a single query

2 Answers2