Home » SQL & PL/SQL » SQL & PL/SQL » reset sequence on sysdate
reset sequence on sysdate [message #21203] Wed, 17 July 2002 22:58 Go to next message
moloy
Messages: 8
Registered: June 2002
Junior Member
can anybody tell how to reset a sequence on the basis of date
say today it start from 1 and reached upto any value,
next day it will once agian start from 1
Re: reset sequence on sysdate [message #21210 is a reply to message #21203] Thu, 18 July 2002 04:48 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Step 1 : Find out the INCREMENT BY parameter for the sequence with the
following query: 
	Select INCREMENT_BY
	From   USER_SEQUENCES
	Where SEQUENCE_NAME = 'EMP_NO_SEQ' ;
This will return 1. It is not always necessary to run the above query tofind out the INCREMENT BY value. You 
can find it out by looking at the column data for which the sequence is used. 

Step 2 : Alter the sequence with a negative INCREMENT BY value: 
	alter sequence EMP_NO_SEQ increment by -1;

Step 3 : Run the PL/SQL block below to reset the 
sequence value to a desired number: 

	DECLARE
		TEMP   Number(10);
	BEGIN
		While(TRUE)
		LOOP
			Select  EMP_NO_SEQ.nextval
			Into     TEMP
			From   DUAL;
			IF ( TEMP = 100) THEN
			    Exit;
			END IF;
		END LOOP;
	EXCEPTION
		When Others Then
			Dbms_output.Put_line(SQLERRM);
	END;
Step 4 : Again reset the INCREMENT BY value to Original 
	alter sequence EMP_NO_SEQ increment by 1;


You can pack all this into One single stored procedure and use dynamic sql to alter the sequences.
Then Use DBMS_job to schedule it ( run it every day, so that the sequence is reset every day).

Re: reset sequence on sysdate [message #21221 is a reply to message #21210] Thu, 18 July 2002 12:33 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
This techniuqe certainly works - but to save some effort - just set the increment number to the number you want to decrement by and then it resets in one step.

select my_seq.nextval from dual;
2125

alter sequence my_seq increment by -2124;

select my_seq.nextval from dual;
1

You can generalize this into a procedure to reset it to any specified number (useful for synchronizing sequences on different databases - e.g. after you've imported data from one to another). Just remember that you can't set the increment by value to "0" (if the sequence is already at the right value). Just code an IF statement to cater for that one.
Previous Topic: How do I call stored procedure with IN and OUT parameter?
Next Topic: Is it a bug?!!!
Goto Forum:
  


Current Time: Thu Apr 25 15:01:22 CDT 2024