Sequence with monthly cycle 1

[TyzA]

Hi,

Is it possible to create a sequence which will reset itself at the start of each month?

Thanks,
Tijs

 

[SantaMufasa]

TyzA,

Oracle sequences have no innate ability to cycle by time -- only by value.

You can, however, cause the sequence to recylce by time in a variety of ways:

1) Use a scheduled task (in Unix, "cron") that runs at mindnight on the morning of the first day of a month that drops and re-creates the sequence at whatever starting value you want.

2) If you have either a trigger or some other PL/SQL procedure that runs regularly, you can introduce a piece of code that checks the SYSDATE, and if it is the first day of the month, it checks the date that the sequence was created (by checking the "CREATED" date in USER_OBJECTS WHERE OBJECT_NAME = '<sequence name>'), and if the the sequence has not yet been created for the month, you can create it in the PL/SQL code with this code:

EXECUTE IMMEDIATE 'create sequence <sequence name>';

Let us know if either of these methods sounds good to you.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Dagon]

I'm not sure a trigger would be a feasible solution because the create sequence statement would require an implicit commit and triggers don't allow commits.

 

[SantaMufasa]

True, Dagon. Thanks for the good catch.

TyzA, ignore my suggestion for the trigger option.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[TyzA]

Thanks guys.

I'll look into the cron jobs
I was thinking in that direction as well.