Posted By

rtperson on 12/02/12


Tagged


Versions (?)

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.

  1.  
  2. next_val NUMBER;
  3. new_next_val NUMBER;
  4. incr NUMBER;
  5. max_key NUMBER;
  6.  
  7. v_code NUMBER;
  8. v_errmsg VARCHAR2(64);
  9.  
  10.  
  11. SAVEPOINT start_transaction;
  12.  
  13. -- get the max PK from the table that's using the sequence
  14. SELECT MAX(library_document_key) INTO max_key FROM library_documents;
  15.  
  16. -- then read nextval from the sequence
  17. EXECUTE IMMEDIATE 'select ld2_seq.nextval from dual' INTO next_val;
  18. DBMS_OUTPUT.PUT_LINE('ld2_seq next_val ' || next_val);
  19.  
  20. -- calculate the desired next increment for the sequence
  21. incr := max_key - next_val + 1;
  22. EXECUTE IMMEDIATE 'ALTER SEQUENCE ld2_seq increment by ' || incr;
  23. EXECUTE IMMEDIATE 'select ld2_seq.nextval from dual' INTO new_next_val;
  24. EXECUTE IMMEDIATE 'ALTER SEQUENCE ld2_seq increment by 1';
  25. DBMS_OUTPUT.PUT_LINE('ld2_seq new_next_val ' || new_next_val);
  26.  
  27.  
  28. ROLLBACK TO start_transaction;
  29. DBMS_OUTPUT.PUT_LINE('Error code ' || v_code || ': ' || v_errmsg);

Report this snippet  

You need to login to post a comment.