Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2011
    Posts
    2

    Unanswered: Sequence holes issue

    Hi,
    I'm posting here an issue that is struggling me during the last days...

    I have a simple sequence that I use to generate consecutive document numbers but somehow I get continuosly holes...

    Probably there is something wrong in my code but I can't find it.

    So, my question is: waiting to find the real bug, is it possible to create a procedure that runs frequently and check if the current value of the sequence is the same of last document and if not, update the current value with the correct number ?

    how it sounds to you ?

    thanks.

  2. #2
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    If you need consecutive numbers a sequence is the wrong solution. A sequence will never be gap-less.

  3. #3
    Join Date
    Jun 2011
    Posts
    2
    I know (now). I'm just looking for a workaround to gain the time needed to change it in something better

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    create your sequence with NOCACHE. It will still have a hole if you rollback, but a lot less.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    Mar 2007
    Posts
    623
    If you really want this, you may be inspired in these threads on AskTom:
    http://asktom.oracle.com/pls/asktom/...#4344043130503
    http://asktom.oracle.com/pls/apex/f?...11000346070688

    Be aware that for assuring no gaps, each transaction asking for number has to wait till the transaction currently holding the most recent number ends. If it would not, there would be a gap if the previous transaction rolled back and did not use the number. See notes about serialization in those threads and do not complain about performance - this bottleneck is (logically) necessary for achieving this requirement.

  6. #6
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Just a side note: none of those solutions will prevent holes when rows are deleted...

Posting Permissions

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