Search code examples
sqloraclesequencesql-execution-plan

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');
Solution

  • 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).