For some reason you may want to reset a sequence during operation.
Scenario
I had a situation where the reference number of a correspondence has to start from 1 in every month.
Code Snippet:
--if its not equal to this month then this is a new entry of this month
IF (lastaction != mm) THEN
--we attempt to reset the sequence
SELECT ref_sequence.NEXTVAL INTO nxt FROM dual;
EXECUTE IMMEDIATE 'alter sequence ref_sequence increment by ' || -nxt || 'minvalue 0';
SELECT ref_sequence.NEXTVAL INTO nxt FROM dual; --move to 0 (counter-intuive)
EXECUTE IMMEDIATE 'alter sequence ref_sequence increment by 1 minvalue 0';
--by now the next value should be 1
END IF;
No comments:
Post a Comment
Feel free to leave a comment