Thursday, March 5, 2015

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.
Source code on github