Search code examples
sqloracle-databaseplsqloracle9iora-01722

Altering Oracle sequence using result of expression

I have two variables 

MAX_TABLE_ID INTEGER;
NEXT_TABLE_ID INTEGER;

I'm reading values into these and then trying to alter a sequence based on them

EXECUTE IMMEDIATE 'ALTER SEQUENCE NEXT_VALIDATED_TABLE_ID INCREMENT BY [MAX_TABLE_ID-NEXT_TABLE_ID]';

Bit I'm getting an "invalid number" error or a SQL command not ended error.

This even happens if I try hardcoded numbers

ALTER SEQUENCE NEXT_VALIDATED_TABLE_ID INCREMENT BY 100-1; or

ALTER SEQUENCE NEXT_VALIDATED_TABLE_ID INCREMENT BY [100-1];

Am i missing something obvious here

Solution

  • I'd try doing the calculation outside the literal, eg

    EXECUTE IMMEDIATE 'ALTER SEQUENCE NEXT_VALIDATED_TABLE_ID INCREMENT BY ' || MAX_TABLE_ID-NEXT_TABLE_ID;