Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2007
    Posts
    63

    Answered: alter sequence after a failed transaction

    if a transaction is failed( During exception ), the sequence value is missed out in between. How to make the sequence values in order even if any exception occur in between randomly.

    I tried including the following stmt in exception block. "Alter sequence < seq name > increment by -1"
    it didnt work even as it goes to prev sequence value in next successful transaction. I know the solution is simple...but need assistance it seems !!

  2. Best Answer
    Posted by Littlefoot

    "Why do you care? A sequence (meaning an Oracle object) guarantees uniqueness, but it can't guarantee the final result will be a gapless sequence of numbers. If you want to bother, you'll have to write your own "program sequence" which might, or might not, be a good substitute.

    Generally speaking, you don't want to do that.

    Here's an interesting reading for you: OTN Forums discussion

    A quick "solution" might look like this:
    Code:
    SQL> CREATE SEQUENCE seq_test START WITH 1 INCREMENT BY 1;
    
    Sequence created.
    
    SQL> SELECT seq_test.NEXTVAL FROM DUAL;
    
       NEXTVAL
    ----------
             1
    
    SQL> SELECT seq_test.NEXTVAL FROM DUAL;
    
       NEXTVAL
    ----------
             2
    
    SQL> SELECT seq_test.NEXTVAL FROM DUAL;
    
       NEXTVAL
    ----------
             3
    
    SQL> -- Suppose that transaction failed here, so you want to "restore" sequence's NEXT value to 3 (again)
    SQL> DECLARE
      2     l_seq   NUMBER;
      3     l_str   VARCHAR2 (100);
      4  BEGIN
      5     SELECT seq_test.NEXTVAL INTO l_seq FROM DUAL;
      6
      7     EXECUTE IMMEDIATE ('drop sequence seq_test');
      8
      9     l_seq := l_seq - 1;
     10
     11     EXECUTE IMMEDIATE
     12        ('create sequence seq_test start with ' || l_seq || ' increment by 1');
     13  END;
     14  /
    
    PL/SQL procedure successfully completed.
    
    SQL> SELECT seq_test.NEXTVAL FROM DUAL;
    
       NEXTVAL
    ----------
             3
    
    SQL>
    However, once again: in my opinion, you do not want to do that."


  3. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,088
    Provided Answers: 4
    Why do you care? A sequence (meaning an Oracle object) guarantees uniqueness, but it can't guarantee the final result will be a gapless sequence of numbers. If you want to bother, you'll have to write your own "program sequence" which might, or might not, be a good substitute.

    Generally speaking, you don't want to do that.

    Here's an interesting reading for you: OTN Forums discussion

    A quick "solution" might look like this:
    Code:
    SQL> CREATE SEQUENCE seq_test START WITH 1 INCREMENT BY 1;
    
    Sequence created.
    
    SQL> SELECT seq_test.NEXTVAL FROM DUAL;
    
       NEXTVAL
    ----------
             1
    
    SQL> SELECT seq_test.NEXTVAL FROM DUAL;
    
       NEXTVAL
    ----------
             2
    
    SQL> SELECT seq_test.NEXTVAL FROM DUAL;
    
       NEXTVAL
    ----------
             3
    
    SQL> -- Suppose that transaction failed here, so you want to "restore" sequence's NEXT value to 3 (again)
    SQL> DECLARE
      2     l_seq   NUMBER;
      3     l_str   VARCHAR2 (100);
      4  BEGIN
      5     SELECT seq_test.NEXTVAL INTO l_seq FROM DUAL;
      6
      7     EXECUTE IMMEDIATE ('drop sequence seq_test');
      8
      9     l_seq := l_seq - 1;
     10
     11     EXECUTE IMMEDIATE
     12        ('create sequence seq_test start with ' || l_seq || ' increment by 1');
     13  END;
     14  /
    
    PL/SQL procedure successfully completed.
    
    SQL> SELECT seq_test.NEXTVAL FROM DUAL;
    
       NEXTVAL
    ----------
             3
    
    SQL>
    However, once again: in my opinion, you do not want to do that.

  4. #3
    Join Date
    Apr 2007
    Posts
    63
    Thank you so much!! Much helpful. Just need it for maintaining a chain of sequence numbers as primary column in detail table without skipping numbers in between.

  5. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,088
    Provided Answers: 4
    a chain of sequence numbers as primary column
    Once again: you don't want to do that. Use a sequence, it'll be unique, your primary key will be just fine.

  6. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,794
    Provided Answers: 11
    If you are looking for an unbroken sequence of numbers to show to users, then you can create a view with the ROW_NUMBER() function. These will be generated when you need them, so you only need to consider the uniqueness in the table itself. Let the display layer deal with the display requirements.

  7. #6
    Join Date
    Apr 2007
    Posts
    63
    Thank you for suggestion!

  8. #7
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,504
    Also dropping and rebuilding the sequence in a procedure WILL cause your application to fail if more then one user is using it and will cause any stored code that references the sequence to go invalid when the drop occurs. A very bad idea.

    If you absolutely must have no holes in the sequence because of a local law then the only way to do it would be to do the following steps when you insert into the table

    LOCK the table exclusively.
    select the max value of the ID field
    insert into the table using the max value from above + 1
    commit to save the record or rollback to not save the record. Either one will remove the lock.

    I do not suggest that you do this because it will slow down access to the table but that is the only way to insure that there are no holes, but again unless you will go to jail if you don't then why do it?
    Last edited by beilstwh; 07-18-16 at 08:47.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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