I don't recall ever writing such a thing, not saying it isn't out there - I just don't recall. And if I did, it is flawed. The only way - the ONLY way - to have a gapless sequence is to use a serial operation:

create table bad_idea( name varchar2(30) primary key, value number not null ) organization index;

insert into bad_idea (name,value) values ( 'my_seq', 0 );


and then to get a sequence:

update bad_id set value = value+1 where name = ? returning value into ?;


A table based approach would easily allow for gaps. Say three transactions are going and we have the values 1..infinity loaded into the table.

transaction 1 gets value 1
transaction 2 gets value 2
transaction 3 gets value 3

transaction 1 and 3 commit
transaction 2 rolls back due to an error

you are at the end of the month (no one is asking for another sequence right now) and closing the books.

uh oh. gap....