Is a sequence incremented in a failed insert?

Here's a sequence

create sequence tq84_failed_insert_seq start with 1 increment by 1;

And an insert statement:

insert into tq84_failed_insert values( tq84_failed_insert_seq.nextval, lpad('-', i, '-') );

If the insert statement fails, is the sequence still incremented?

Let's try it with a test. The table:

create table tq84_failed_insert( i number primary key, j varchar2(20) );

Some insert statements:

insert into tq84_failed_insert values (5, lpad('-', 5, '-')); insert into tq84_failed_insert values (9, lpad('-', 9, '-'));

and an anonymous block:

begin for i in 1 .. 10 loop begin insert into tq84_failed_insert values( tq84_failed_insert_seq.nextval, lpad('-', i, '-') ); exception when dup_val_on_index then null; end; end loop; end; /

After running this anonymous block, the table contains:

select * from tq84_failed_insert order by i;

Returning:

I J ---------- -------------------- 1 - 2 -- 3 --- 4 ---- 5 ----- 6 ------ 7 ------- 8 -------- 9 --------- 10 ----------

So, the value of I is ascending in steps of 1, showing that the value of netxtval is "wasted" if the insert statement fails.

Links

Source code on github