Oracle change increment_by on a sequence

Question

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.

have a look at [this question](http://stackoverflow.com/questions/10383091/how-can-i-alter-a-sequence-in-dynamic-sql) — Aleksej, Dec 15 '16 at 11:55
You can't have an expression for the `increment by`. You also should specify numbers there `'1000000000'` is a string, not a number. You can also not pass a complete SQL query to `to_number()`. Even if you _could_ use a query for `increment by` calling to_number() would be totally useless as `last_number` is already a number, there is no need to convert that to a number — , Dec 15 '16 at 12:11

score 3 · Accepted Answer · answered Dec 15 '16 at 12:08

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;

Oracle change increment_by on a sequence

1 Answers1