0

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Ron
  • 19
  • 3
  • Please, describe what do you mean by "show value all of them". Why `schema.sequence_name.currval` is not sufficient for you? And please show your current code – astentx Oct 11 '21 at 06:02
  • schema.sequence_name.currval is sufficient for me, but how to use this syntax when I have 9999... sequences? Do I have to use this sintax for every sequence? – Ron Oct 11 '21 at 06:21
  • One also begins to wonder what is the business problem being addressed . . . See [xy problem](https://xyproblem.info/) – EdStevens Oct 11 '21 at 13:30

2 Answers2

1

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.

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
  • Is there possibilities not use any tables like 'user_sequences' and take vale only from currval? – Ron Oct 11 '21 at 06:21
  • 1
    you can consecutively execute `seqxxx.currval` after executing `seqxxx.nextval` at least once for the current session @Ron , but you'll get a different number after executing `seqxxx.nextval` . So, the answer is **No** – Barbaros Özhan Oct 11 '21 at 06:34
  • 2
    @Ron - `last_number` gives you last cached number (it's essentially a number saved to disk as a safe place to restart after a restart or crash, to ensure a value still can't be reissued), so it might be higher than the last number actually issued by any `nextval` call. But that might be close enough for you. And if you're using RAC, `currval` shown in a session in one instance might be lower than a value already issued on another instance. – Alex Poole Oct 11 '21 at 07:00
  • _"Is there possibilities not use any tables like 'user_sequences'"_ - give then other things you've said, you don't want to hard-code a list of all of your sequences. If that is the case, then the answer is "no". If you don't want to hard-code all of the sequence names, then the only alternative is to get them from a data dictionary table. – EdStevens Oct 11 '21 at 13:00
0

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.

EdStevens
  • 3,708
  • 2
  • 10
  • 18