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 check Nextval & Curval - Psedo code in Sequence

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

  1. Puru

    Puru Forum Advisor

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

    I have created sequence called Emp_Seq, now I want check the NextValue and current value. Let us know how to write the query:eek:
     
  2. M_Anas_O

    M_Anas_O Forum Advisor

    Messages:
    95
    Likes Received:
    9
    Trophy Points:
    160
    Location:
    Austria
    Select Emp_Seq.nextval
    from dual;


    select Emp_Seq.currval
    from dual;
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    The first generates the new sequence value which is not what the poster wants to do. What is desired is to show what the current (last) generated value is and the next value to generate. The following query will do that:

    Code (SQL):
     
    SELECT sequence_name, last_number, last_number+increment_by next_val
    FROM user_sequences;

     
     
  4. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    How can i see the current value at start of the session, Emp_Seq.currval is not working .
    If we use Emp_Seq.nextval at start and if we use the same , gaps are maintaining. How to rectify this problem.

    Thanks
    Kiran Marla
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Of course currval is not working; it can't as you haven't generated a sequence value with nextval yet. As I said in a prior post use THIS query to see where the sequence values are:

    Code (SQL):

    SELECT sequence_name, last_number, last_number+increment_by next_val FROM user_sequences;
     
    To answer your other question read here: http://oratips-ddf.blogspot.com/2008/06/out-of-sequence.html