rtperson on 12/02/12
Last Edited at 12/02/12 12:39pm
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
Comment:
You need to login to post a comment.