Advanced Search

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

/ Published in: PL/SQL
Save to your folder(s)
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.

Expand | Embed | Plain Text

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

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.