DZone Snippets is a public source code repository. Easily build up your personal collection of code snippets, categorize them with tags / keywords, and share them with the world
How To Change The Value Of A Sequence In PL/SQL
// I'm pretty meh about Oracle's PL/SQL, but its inability to reference and change sequences // has been pretty frustrating over the years. Until now. Thanks to the Puget Sound Oracle // Users Group's website (psoug.org) for showing me how to do this.
declare next_val NUMBER; new_next_val NUMBER; incr NUMBER; max_key NUMBER; v_code NUMBER; v_errmsg VARCHAR2(64); BEGIN SAVEPOINT start_transaction; -- get the max PK from the table that's using the sequence select max(library_document_key) into max_key from library_documents; -- then read nextval from the sequence EXECUTE IMMEDIATE 'select ld2_seq.nextval from dual' into next_val; DBMS_OUTPUT.PUT_LINE('ld2_seq next_val ' || next_val); -- calculate the desired next increment for the sequence incr := max_key - next_val + 1; EXECUTE IMMEDIATE 'ALTER SEQUENCE ld2_seq increment by ' || incr; EXECUTE IMMEDIATE 'select ld2_seq.nextval from dual' into new_next_val; EXECUTE IMMEDIATE 'ALTER SEQUENCE ld2_seq increment by 1'; DBMS_OUTPUT.PUT_LINE('ld2_seq new_next_val ' || new_next_val); commit; EXCEPTION WHEN OTHERS THEN ROLLBACK to start_transaction; DBMS_OUTPUT.PUT_LINE('Error code ' || v_code || ': ' || v_errmsg); end;