Advanced Search

Return to Snippet

Revision: 61180
at December 2, 2012 12:39 by rtperson
Initial Code 
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;
Initial URL 

                                

                                                            

                                    

                                        

                                        Initial Description
                                        

                                    

                                    
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.

                                

                                                            

                                    

                                        

                                        Initial Title
                                        

                                    

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

                                

                                                            

                                    

                                        

                                        Initial Tags
                                        

                                    

                                    

                                

                                                            

                                    

                                        

                                        Initial Language
                                        

                                    

                                    
PL/SQL