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?
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.
You can use
user_sequences
data dictionary view such asas long as those sequences are in your current schema. Otherwise, that might be replaced by
all_sequences
ordba_sequences
depending on the situation or your need.