1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How to know Current Value in sequence

Discussion in 'SQL PL/SQL' started by Puru, Oct 28, 2010.

  1. Puru

    Puru Forum Advisor

    Messages:
    32
    Likes Received:
    1
    Trophy Points:
    90
    Dear All,

    We can check the next value in sequence by using Emp_seq.nextval, like this how to check the current value
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You generate the next value by calleing emo_seq.nextval, you're not 'checking' it. As I have said before you query user_sequences to find current sequence information:

    Code (SQL):
     
    SELECT sequence_name, last_number curr_val, increment_by,
    last_number + Increment_by next_val
    FROM user_sequences
    WHERE sequence_name LIKE 'EMP%';
     
    That query, which I have posted in more than one similar thread, will tell you the information you asked for.

    Please also read here:

    http://oratips-ddf.blogspot.com/2008/06/out-of-sequence.html

    to better understand sequences.
     
  3. Puru

    Puru Forum Advisor

    Messages:
    32
    Likes Received:
    1
    Trophy Points:
    90
    Dear David,

    Thank your for imm response, I have tried the quries the result is as follows
    SQL> select seq_emp.nextval from dual;

    NEXTVAL
    ----------
    21

    SQL> select seq_emp.currval from dual;

    CURRVAL
    ----------
    21

    But my question is the NEXT VALUE and CURRENT VALUES shouldn't be same. If Curval is 21, the Nextval should be 22. Kindly let me know how to achive it.




     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Those are not the queries I said to execute; this one is:

    Code (SQL):
    SELECT sequence_name, last_number curr_val, increment_by,last_number + Increment_by next_val
    FROM user_sequences
    WHERE sequence_name LIKE 'EMP%';
     
    That query will return a set of values showing the current value of the sequence, the increment and the next value it will generate. Using nextval and currval will NEVER give you what you want because every call to nextval sets currval to the currently generated sequence value. Once nextval is called it automatically sets currval to the same value.

    The nextval WILL be 22, but so will currval afterwards. Please read the blog post I referenced before asking any further questions about sequences.