How to use Oracle DB sequences without losing the next sequence number in case of roll-back

Question

Question

How to use Oracle DB sequences without losing the next sequence number in case of roll-back?

Facts collected

1 - In Oracle, we can create a sequence and use two main calls (NEXTVAL) to get the next sequence value and (CURRVAL) to get the current sequence value.

2 - When we call (NEXTVAL) will always get the next number and we will lose it if there is a rollback. In other words, Oracle sequence does not care if there is a roll-back or commit; whenever you are calling it, it will give a new number.

Possible answers I found so far

1 - I was thinking to create a simple table with one column of type (NUMBER) to service this purpose. Simply pick the value and use it. If operation succeeded I will increment column value. Otherwise, I will keep it as it is for the next application call.

2 - Another way I found here (How do I reset a sequence in Oracle?) is to use (ALTER SEQUENCE) like the following if I want to go one step back.

That is, if the sequence is at 101, I can set it to 100 via

ALTER SEQUENCE serial INCREMENT BY -1;
SELECT serial.NEXTVAL FROM dual;
ALTER SEQUENCE serial INCREMENT BY 1;

Conclusion

Are any of the suggested solutions is good? Is their any better approach?

Answer 1

You can create a sequence table.

CREATE TABLE SEQUENCE_TABLE
  (SEQUENCE_ID NUMBER, 
   SEQUENCE_NAME VARCHAR2(30 BYTE), 
   LAST_SEQ_NO NUMBER);

And in your PL/SQL block, you can get the sequence using below lines of code,

declare
    CURSOR c1 IS
       SELECT last_seq_no
         FROM sequence_table
        WHERE sequence_id = 21
          FOR UPDATE NOWAIT;
       v_last_seq_no NUMBER;
       v_new_seq_no NUMBER;
    resource_busy EXCEPTION;
    PRAGMA EXCEPTION_INIT(resource_busy, -54);
BEGIN
    LOOP
        BEGIN
           OPEN c1;
           FETCH c1 INTO v_last_seq_no;
           CLOSE c1;
           v_new_seq_no := v_last_seq_no+1;
           EXIT;
        EXCEPTION
            WHEN resource_busy THEN
                NULL;
                --or something you want to happen
        END;
   END LOOP;
   --after the this line, you can put an update to the sequence table and be sure to commit/rollback at the end of the pl/sql block;
END;
/


ROLLBACK;
--or
COMMIT;

Try to run the PL/SQL code above in two oracle sessions to understand. Basically, if Oracle DB session 1 will run the code, the record queried from the cursor will be lock. So, if other session will run the same code, that session will wait for session 1's rollback/commit to finish running the code. Through this, two sessions won't have the same sequence_no and you have a choice not to update the sequence if you issue a rollback for some reasons.

Answer 2

From my point of view, you should use a sequence and stop worrying about gaps.

From your point of view, I'd say that altering the sequence is worse than having a table. Note that access to that table must be restricted to a single user, otherwise you'll get duplicate values if two (or more) users access it simultaneously.

Here's a sample code; have a look, use/adjust it if you want.

SQL> create table broj (redni_br number not null);

Table created.

SQL>
SQL> create or replace function f_get_broj
  2     return number
  3  is
  4     pragma autonomous_transaction;
  5     l_redni_br   broj.redni_br%type;
  6  begin
  7         select b.redni_br + 1
  8           into l_redni_br
  9           from broj b
 10     for update of b.redni_br;
 11
 12     update broj b
 13        set b.redni_br = l_redni_br;
 14
 15     commit;
 16     return (l_redni_br);
 17  exception
 18     when no_data_found
 19     then
 20        lock table broj in exclusive mode;
 21
 22        insert into broj (redni_br)
 23             values (1);
 24
 25        commit;
 26        return (1);
 27  end f_get_broj;
 28  /

Function created.

SQL> select f_get_broj from dual;

F_GET_BROJ
----------
         1

SQL> select f_get_broj from dual;

F_GET_BROJ
----------
         2

SQL>