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 netxtval is "wasted" if the insert statement fails.
Links
Source code on github
