Posted By

rtperson on 12/02/12

Tagged

Versions (?)

Last Edited at 12/02/12 12:39pm

Statistics

Viewed 1098 times

Favorited by 0 user(s)

Related snippets

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

/ Published 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.

Highlighted TextExpand

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;

Report this snippet Tweet

Comments

Subscribe to comments

Comment:

You need to login to post a comment.