CODEWITHSUNDEEP
sqloracle-databasesequencesql-execution-plan
Raghav
Raghav

Reputation: 207

oracle sequence NEXTVAL behavior in case of query execution failure

If Oracle fails to execute the below query due to whatever reason, will the supplier_seq get incremented? When exactly does Oracle increment sequences?

INSERT INTO suppliers
(supplier_id, supplier_name)
VALUES
(supplier_seq.NEXTVAL, 'Kraft Foods');

Upvotes: 0

Views: 2600

Answers (3)

charles chen
charles chen

Reputation: 82

Every time you called supplier_seq.NEXTVAL, it increased.

If you want to insert into a table with sequence, you may find you will fail with error unique constraint violated. However, when you tried on the second time, it would be a success, that's because sequence need check at the first time you use it. Here one of the possible solutions:

before insert, do the select query:

select (you sequence name) from table.

then,

insert into table value(...).

It will work.

Upvotes: 0

user330315
user330315

Reputation:

The sequence is incremented as soon as nextval is called. This increment will never be rolled back. The value is "gone". The only situation where this value could ever be generated again by the sequence, is if the sequence is set to CYCLE and it wraps around.

due to whatever reason

The reason does matter.

If the statement fails because there is a syntax error or there is no table named suppliers, or if the user does not have the privilege to insert into suppliers then the sequence will not be incremented (because nextval is never actually called because execution is aborted in an earlier step).

Upvotes: 2

Yigitalp Ertem
Yigitalp Ertem

Reputation: 2039

Independently from whether query fails or not, Oracle Sequences are synchronously incremented in all sessions, right when they are called.

However, if there is a syntax error in your statement (wrong tablename, column name etc.), sequence will stay the same since the query won't be executed.

Upvotes: 2

Related Questions