Search code examples
sqloracle-databaseddldatabase-sequence

Oracle change increment_by on a sequence

ALTER SEQUENCE my_sequence
    INCREMENT BY '1000000000' - TO_NUMBER(SELECT last_number FROM all_sequences WHERE sequence_name='my_sequence');

Can someone explain to me why it throws 'Incorrect number'? I tried putting TO_NUMBER everywhere, I tried without it at all, I tried without '' on the big number, every combination I can think of, still errors, doesn't make any sense to me at all. The subquery works, I checked.

Solution

  • use pl/sql block instead:

    DECLARE
    INC NUMBER;
BEGIN
    SELECT  1000000000 - LAST_NUMBER
    INTO    INC
    FROM    USER_SEQUENCES 
    WHERE   SEQUENCE_NAME='my_sequence';

    EXECUTE IMMEDIATE 'ALTER SEQUENCE my_sequence INCREMENT BY '||INC;
END;