| Complex sequence [message #21570] |
Wed, 14 August 2002 07:07  |
scott
Messages: 73
Registered: September 1999
|
Member |
|
|
I need to write a sequence that 1) begins at 1 but is padded with 4 zeros. The first number would be 00001, then 00002 and so on. Also 2) The sequence must be reset to 00001 on Dec 31 every year.
Does anyone have know how to do this? Thanks in advance.
Scott
|
|
|
|
| Re: Complex sequence [message #21572 is a reply to message #21570] |
Wed, 14 August 2002 08:59  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
You can use a sequence object and then just format the value:
sql>create sequence test;
Sequence created.
sql>select to_char(test.nextval, 'FM00000') from dual;
TO_CHA
------
00001
sql>select to_char(test.nextval, 'FM00000') from dual;
TO_CHA
------
00002
However, a sequence does not guarantee you a gap-free sequence of numbers, just unique numbers. If you need gap-free, you'll need to store a value in a table and increment it yourself factoring in multi-user contention if applicable.
To reset at year end, either drop and recreate the sequence, or alter the increment value to a negative value temporarily to reset the starting value. With the table approach, you would just update the value. The job could be scheduled to happen automatically using DBMS_JOB.
|
|
|
|
| Re: Complex sequence [message #21607 is a reply to message #21570] |
Fri, 16 August 2002 08:24 |
Nick
Messages: 64
Registered: February 2000
|
Member |
|
|
Step 1) craete a procedure that would check the system date and drop the sequence and create a new sequence.
2) Ask your DBA to change init.ora file so that you can run dbms_job package.
3) grant execute on dbms_job to YOURUSER
-- after connecting as sys.
Let me know if u have not understood this bit.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
