As it has been said before, you can use seq_name.CURRVAL, but there are two "gotchas" with this:
1. You must have isued at least one seq_name.NEXTVAL *in the same session*.
2. What you get from your seq_name.CURRVAL is only the last value *you* have gotten from the sequence, this value does not tell you anything about *the current value of the sequence as such*! The next value you'll get from the sequence might be your CURRVAL+1, but it might also be your CURRVAL+176543 or any other value!
However you can get the next value that will be returned (to any user) by the sequence at the next request if you login as user SYS. There is an object called V$_SEQUENCES owned by SYS that can't be reached by any other user. This fixed view keeps track of cached portions of sequence values that are stored in the SGA and is not shown in any DBA_/ALL_/USER_* or V$* views. But even with this view there are two "gotchas":
1. It will show only the sequences that were not defined with NOCACHE option.
2. It will only show the sequences that are currently cached in a shared pool. For example, it will not show the sequences that were not yet used since the database startup or the sequences that are so rarely used that their cached values have been flushed out of the shared pool by other objects. And of course, it will not show any sequence immediately after you isue ALTER SYSTEM FLUSH SHARED POOL
If we neglect this second "gotcha" and bare the first one in mind we can get the next value to be returned by the sequence either using DBA_SEQUENCES (for noncached sequences) or V$_SEQUENCES (for cached ones). If we don't know if sequence is created with or without cache option, but want to get information about the next value with single SQL select, you can use the following:
SELECT nextvalue FROM v$_sequences
WHERE sequence_owner = 'SCOTT' AND sequence_name = 'MY_SEQ'
AND cache_size > 0
SELECT last_number AS nextvalue FROM dba_sequences
WHERE sequence_owner = SCOTT' AND sequence_name = 'MY_SEQ'
AND cache_size = 0;
Jurij Modic ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?