Oracle ORA-04002 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-04002: INCREMENT Must Be a Nonzero Integer

ORA-04002 is thrown by Oracle when you attempt to create or alter a sequence with an INCREMENT BY value of zero or a non-integer (decimal) number. Oracle sequences are strictly integer-based counters, so the increment must be a nonzero whole number — positive for ascending sequences, negative for descending ones. This error most commonly surfaces during DDL scripting, dynamic SQL generation, or automated deployment pipelines.

Top 3 Causes and Fixes

Cause 1: INCREMENT BY Set to Zero

The most frequent cause — specifying 0 as the increment value.

-- ❌ Triggers ORA-04002
CREATE SEQUENCE orders_seq
  START WITH 1
  INCREMENT BY 0;   -- Zero is not allowed

-- ✅ Correct: use any nonzero integer
CREATE SEQUENCE orders_seq
  START WITH 1
  INCREMENT BY 1
  MAXVALUE 9999999999
  NOCYCLE
  CACHE 20;

-- ✅ Correct: descending sequence (negative increment)
CREATE SEQUENCE orders_seq_desc
  START WITH 1000
  INCREMENT BY -1
  MINVALUE 1
  NOCYCLE;

-- Fix an existing sequence
ALTER SEQUENCE orders_seq INCREMENT BY 1;

Cause 2: Decimal (Non-Integer) INCREMENT Value

Passing a decimal value like 0.5 or 2.5 to INCREMENT BY will also raise ORA-04002.

-- ❌ Triggers ORA-04002
CREATE SEQUENCE my_seq
  START WITH 1
  INCREMENT BY 0.5;   -- Decimals not supported

-- ✅ Always use whole integers
CREATE SEQUENCE my_seq
  START WITH 1
  INCREMENT BY 1;

-- ✅ If you need larger steps, use a whole number
CREATE SEQUENCE my_seq_step10
  START WITH 10
  INCREMENT BY 10
  NOCYCLE
  CACHE 50;

Cause 3: Invalid Variable in Dynamic SQL

In PL/SQL or shell scripts, a variable holding the increment value may be NULL, 0, or a decimal — causing the error at runtime.

-- ❌ Unsafe: no validation before EXECUTE IMMEDIATE
DECLARE
  v_inc NUMBER := 0;
  v_sql VARCHAR2(500);
BEGIN
  v_sql := 'CREATE SEQUENCE dyn_seq INCREMENT BY ' || v_inc;
  EXECUTE IMMEDIATE v_sql;  -- ORA-04002 raised here
END;
/

-- ✅ Safe: validate before executing
DECLARE
  v_inc NUMBER := 0;  -- Simulating bad input
  v_sql VARCHAR2(500);
BEGIN
  -- Guard clause: reject zero, NULL, or decimals
  IF v_inc IS NULL OR v_inc = 0 THEN
    RAISE_APPLICATION_ERROR(-20001,
      'INCREMENT must be a nonzero integer.');
  END IF;

  IF v_inc != TRUNC(v_inc) THEN
    RAISE_APPLICATION_ERROR(-20002,
      'INCREMENT must be a whole integer, not a decimal.');
  END IF;

  v_sql := 'CREATE SEQUENCE dyn_seq '
        || 'START WITH 1 '
        || 'INCREMENT BY ' || TO_CHAR(TRUNC(v_inc)) || ' '
        || 'NOCYCLE CACHE 20';

  EXECUTE IMMEDIATE v_sql;
  DBMS_OUTPUT.PUT_LINE('Sequence created successfully.');
END;
/

Quick Fix Checklist

Symptom	Fix
`INCREMENT BY 0`	Change to any nonzero integer (e.g., `1`)
`INCREMENT BY 0.5`	Round to nearest integer (e.g., `1`)
Dynamic SQL variable is `NULL`	Add NULL check before `EXECUTE IMMEDIATE`
Script auto-generates bad value	Validate and sanitize all inputs

-- Verify your sequence settings after fix
SELECT SEQUENCE_NAME,
       MIN_VALUE,
       MAX_VALUE,
       INCREMENT_BY,
       CYCLE_FLAG,
       CACHE_SIZE
FROM   USER_SEQUENCES
WHERE  SEQUENCE_NAME = 'MY_SEQ';

Prevention Tips

Use a standard DDL template — Define a team-wide sequence creation template where INCREMENT BY is always explicitly set to a validated nonzero integer. Never rely on defaults from scripts that accept user or environment variable input without validation.
Wrap dynamic sequence creation in a utility procedure — Centralize all dynamic sequence DDL in a single validated stored procedure (using DBMS_ASSERT.SIMPLE_SQL_NAME to prevent SQL injection) so bad increment values are caught before they reach the database engine.

Related Errors

ORA-04001 — Invalid sequence start value
ORA-04003 — INCREMENT exceeds the range between MINVALUE and MAXVALUE
ORA-04006 — START WITH value is greater than MAXVALUE
ORA-04007 — MINVALUE must be less than MAXVALUE
ORA-02289 — Sequence does not exist

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

DEV Community