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

Roger has posted 34 posts at DZone. View Full User Profile

How To Change The Value Of A Sequence In PL/SQL

12.02.2008
| 26916 views |
  • submit to reddit
        // 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;