How to get a sequence value
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: How to get a sequence value

  1. #1
    Join Date
    Apr 2001
    Posts
    13

    Question

    Hi!

    I need to get the current value of a sequence without increasing the value. How do I do this with SQL?

    Thanks!

  2. #2
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    Hi,

    use CURRVAL instead of NEXTVAL.

    As far I know you must select seq.NEXTVAL in session before you can select seq.CURRVAL otherwise error "ORA-08002: sequence seq.CURRVAL is not yet defined in this session" occurs.

    HTH,
    Ales


  3. #3
    Join Date
    Jun 2001
    Posts
    1

    Arrow

    Hi,

    Select seq_name.CURRVAL from dual;

    If you are querying for the first time after creation of the sequence then issue like this

    Select seq_name.NEXTVAL from dual;

    then you can use

    Select seq_name.CURRVAL from dual;

    Thanks

    Srinvias


  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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
    UNION
    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?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width