Retrieving CURRVAL and NEXTVAL in a single query

sql oracle nextval
tworley1977 picture tworley1977 · Jun 21, 2014 · Viewed 14.4k times · Source

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

JasonMArcher picture JasonMArcher · Dec 19, 2014

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.

Related questions

Get list of all tables in Oracle?

How do I query an Oracle database to display the names of all tables in it?

Read More
How do I limit the number of rows returned by an Oracle query after ordering?

Is there a way to make an Oracle query behave like it contains a MySQL limit clause? In MySQL, I can do this: select * from sometable order by name limit 20,10 to get the 21st to the 30th rows (skip the …

Read More
Oracle SQL: Update a table with data from another table

Table 1: id name desc ----------------------- 1 a abc 2 b def 3 c adf Table 2: id name desc ----------------------- 1 x 123 2 y 345 In oracle SQL, how do I run an sql update query that can update Table 1 with Table 2's name and desc using …

Read More