How to get the currval for all sequences in one SQL query

Posted on 2010-04-07
Medium Priority
Last Modified: 2013-11-11
I need a sql query that will give me the currval of all sequences in one go. How might I do that? I'm only aware of how to get currval like this:

SELECT MySequence.CURRVAL from dual;

Thanks for any assistance.
Question by:nanderEire
  • 2
  • 2
LVL 78

Accepted Solution

slightwv (䄆 Netminder) earned 92 total points
ID: 30014014
CURRVAL is only good for the same session that executed NEXTVAL.

Look at user_sequences:
select sequence_name, last_number from user_sequences;

Author Comment

ID: 30014185
but isn't that the last number in the cache, not necessarily the current number?
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 30014332
current number is relative to the session that created it.

Log into sqlplus from two separate windows and do the following:

select mySeq.nextval from dual;
select mySeq.curval from dual;

Both windows will show a different CURRVAL.
LVL 74

Expert Comment

ID: 30025685
IF you have called nextval for every sequence then you could try something like this...

SELECT sequence_name,
DBMS_XMLGEN.getxmltype('select ' || sequence_name || '.currval from dual'),
current_value FROM user_sequences;

note, all caveats detailed above by slightwv still apply here,

if any of the sequences have NOT been defined by a call to nextval you'll get an ORA-8002 when you try to query currval
LVL 74

Assisted Solution

sdstuber earned 88 total points
ID: 30025890
oops ,small correction to query above ( I forgot the alias x )

SELECT sequence_name,
DBMS_XMLGEN.getxmltype('select ' || sequence_name || '.currval x from dual'),
FROM user_sequences;

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Via a live example, show how to take different types of Oracle backups using RMAN.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question