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.

Answer 1

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.

Answer 2

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.