Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2003
    Posts
    2

    Unanswered: Sequence Current value

    We have a database with all tables assigned a primary key as a sequence number. Each table has its own sequence number. There are several ways the data are loaded to the database. Hence, we often get into the problem of sequence number being off of the max value of the primary key field. So, I have hard time synchronizing the seq. value with that of the primary key field max value. However, if I know the current value of the seq., I can develope a package (Seq. Manager) and automate the synchronization. If I select seq.nextval, I loose a seq. number. If the seq. is not in the session, then I don't know how to find the seq. current value. Please some one help.

    Thanks
    Gopal

  2. #2
    Join Date
    Oct 2003
    Location
    St.Louis,MO
    Posts
    120
    sequence_name.currval

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296
    as most DBAs will tell you, it would be important to realize that missing a sequence is meaningless and you should not be relying on perfect sequence order in your business rules.

    the sequence is there strictly to be used as a substitute for a PK and would be used behind the scenes/application. So by having a gap between 10000 and 10010 should not matter to anyone.

    (I probably said all this the wrong way, but I hope you get my meaning)
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  4. #4
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    What the Duck is trying to say (forgive me if I'm wrong J) is that a sequence doesn't guarantee contiguous numbers. There are many reasons and situations where it can 'skip' a block of values.

    If you have a PK based on a sequence which is getting duplicates, that's a different matter. It is easy to suggest a trigger to guarantee unique values but the reality is that an application may be pulling a direct read of a seq result (via a column) or via application code and so make a trigger even more problematic.

    As J says, the numbers were never guaranteed to be contiguous, only to be unique.

    Hth
    Bill

  5. #5
    Join Date
    Nov 2003
    Location
    Bangalore, INDIA
    Posts
    333

    Thumbs up Re: Sequence Current value

    Hi,

    SELECT sequence_name.currval CURRENT,
    FROM dual;

    Originally posted by lrgops
    We have a database with all tables assigned a primary key as a sequence number. Each table has its own sequence number. There are several ways the data are loaded to the database. Hence, we often get into the problem of sequence number being off of the max value of the primary key field. So, I have hard time synchronizing the seq. value with that of the primary key field max value. However, if I know the current value of the seq., I can develope a package (Seq. Manager) and automate the synchronization. If I select seq.nextval, I loose a seq. number. If the seq. is not in the session, then I don't know how to find the seq. current value. Please some one help.

    Thanks
    Gopal
    SATHISH .

  6. #6
    Join Date
    Nov 2003
    Location
    Rotterdam, Netherlands
    Posts
    127
    Hi,

    I fully agree with DUCK ...

    Anyways there are 2 work arounds for you

    1. Take the max value of the primary key from the respective table!!

    2. When you create the sequence give the cache size as 2
    i.e create sequence tryseq start with 1 increment by 1 cache 2;
    2 is the minimum value.

    With this u can query USER_SEQUENCES view and the LAST_NUMBER column will give u the last number in teh cache, this will be either the immediate next value or just one more than that...
    i.e if LAST_NUMBER is 3, then currval will be either 1 or 2.
    JUST SEE IF YOU CAN USE THIS!!! Still not 100% fool proof.

    Regards

  7. #7
    Join Date
    Jul 2003
    Posts
    2,296
    Originally posted by billm
    What the Duck is trying to say (forgive me if I'm wrong J) is that a sequence doesn't guarantee contiguous numbers. There are many reasons and situations where it can 'skip' a block of values.

    If you have a PK based on a sequence which is getting duplicates, that's a different matter. It is easy to suggest a trigger to guarantee unique values but the reality is that an application may be pulling a direct read of a seq result (via a column) or via application code and so make a trigger even more problematic.

    As J says, the numbers were never guaranteed to be contiguous, only to be unique.

    Hth
    Bill
    Thanks Bill!
    That is what I was trying to say.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  8. #8
    Join Date
    Nov 2003
    Posts
    2
    Thanks for everyone replied to help me. Before asking my question in the forum, I was 99% convinced that there is no way to get accurate current value without compromising seq.nextval. I took my last 1% chance to find the real answer from oracle experts. Well, it looks like it is 100% that there is no way to get the accurate current value without loosing the nextval. Ofcourse, it is a good compromise to loose a seq. value every time I select the nextval. Thanks once again for the help

Posting Permissions

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