Skip to main content

Sequence NEXTVAL should not be used in conditional results

ZPA rule for PL/SQL and Oracle SQL · SequenceNextvalInConditionalResult

In a SQL statement, Oracle increments a sequence once for every row that references its NEXTVAL, whichever branch of a CASE or DECODE the row takes. A NEXTVAL written in a conditional result is therefore consumed for every row, also for those that get the other result: the sequence advances faster than expected and leaves gaps.

This rule reports NEXTVAL in a result of a CASE expression, or of the built-in DECODE, in a SQL statement, inside PL/SQL or standalone in a script, when the name is a sequence the project creates. A CASE in a PL/SQL expression is not reported: PL/SQL evaluates only the branch it takes. Sequences outside the analyzed project are not recognized. Filter the rows that need a value in the WHERE clause instead.

Examples​

Noncompliant Code Example
create procedure label_new_crates is
begin
insert into crate_label (crate_id, label_no)
select c.id, case when c.status = 'NEW' then label_seq.nextval end -- Noncompliant
from crate c;
end;
Compliant Solution
create procedure label_new_crates is
begin
insert into crate_label (crate_id, label_no)
select c.id, label_seq.nextval
from crate c
where c.status = 'NEW';
end;