Links

   Quran Explorer - Interactive Audio Recitations & Translations

Wednesday, September 1, 2010

Oracle - Resetting a sequence

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