Nightly re-setting sequence number [message #9509] |
Mon, 17 November 2003 10:56 |
Mike Nibeck
Messages: 49 Registered: May 2002
|
Member |
|
|
We have a process that dictates that we need to maintain a unique identifies (Batch Number), that gets reset every night. We could use a DB sequence, and then run a job at midnight to reset (drop/re-add) it, but that seems awful clumsy. We could also attempt to manage it progrematicaly, and keep the most recent value in a table (along with last date used), but that also seems a bit clunky.
Any suggestions??
- Mike
|
|
|
Re: Nightly re-setting sequence number [message #9511 is a reply to message #9509] |
Mon, 17 November 2003 11:57 |
Thiru
Messages: 1089 Registered: May 2002
|
Senior Member |
|
|
Can you recycle the sequence at the maxvalue?
eg)create sequence test_seq minvalue 1 start with 1 increment by 1 maxvalue 5 nocache cycle;
SQL> select test_seq.nextval from dual;
NEXTVAL
----------
5
1 row selected.
SQL> select test_seq.nextval from dual;
NEXTVAL
----------
1
1 row selected.
|
|
|
|