CODEWITHSUNDEEP
sqloracle-databaseddldatabase-sequence
Shadov
Shadov

Reputation: 5566

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.

Upvotes: 1

Views: 1143

Answers (1)

hmmftg
hmmftg

Reputation: 1764

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;

Upvotes: 3

Related Questions