How to get values of all sequences using currval?-CodePudding

In example: have 3 sequences (seq1, seq2, seq3) and I would like make one query showing their name and their current value value?

The basic syntax is

select schema.seq_name.currval from dual

But how to show value of all of them?

CodePudding user response：

You can use user_sequences data dictionary view such as

SELECT s.sequence_name, s.last_number AS curr_value
  FROM user_sequences s
 WHERE s.sequence_name IN ('SEQ1','SEQ2','SEQ3')

as long as those sequences are in your current schema. Otherwise, that might be replaced by all_sequences or dba_sequences depending on the situation or your need.

CodePudding user response：

In spite of my deep reservations about why you would even need to do this, I tried to put together a pl/sql procedure as a demo - partly just to challenge myself. When I ran into an issue that I thought would be simple, just needing a fresh pair of eyes, I posted on OTN. While they addressed my coding issue, one reply summed up your problem:

you can not select currval without calling nextval. currval is the function that returns last used sequence value via nextval function on your session.