Re: managing sequences

From: Frans H. <fransh_at_hotmail.com>
Date: 29 Oct 2003 01:18:48 -0800
Message-ID: <31abf0e5.0310290118.52c3e6f2@posting.google.com>

"Norman Dunbar" <Norman_at_RE-MO-VE.BountifulSolutions.co.uk> wrote in message news:<pan.2003.10.28.08.52.16.410470_at_RE-MO-VE.BountifulSolutions.co.uk>...
> On Mon, 27 Oct 2003 17:36:44 +0100, Morgan GOARIN wrote:
>
> > Hi
> > I am using sequences in a PL/SQL stored procedure and I need to reset my
> > sequence. Does anybody know the way to re-initialize an Oracle sequence
> > without having to drop and recreate it ?
> > Thanks in advance
> > Morgan Goarin
>
>
> Here you are :
>
> http://www.jlcomp.demon.co.uk/faq/resetseq.html
>
> Enjoy !
>
>
> Cheers,
> Norman.

Hi, or modify it a bit so you can start at any given number:

CREATE OR REPLACE PROCEDURE reset_sequence (sequencename IN VARCHAR2

                                            ,startvalue in integer) as    
	curr_val INTEGER;
	string   varchar2(25);
BEGIN
	EXECUTE IMMEDIATE 'alter sequence ' ||sequencename||' MINVALUE 0';
	EXECUTE IMMEDIATE 'SELECT ' ||sequencename ||'.nextval FROM dual' INTO curr_val;
	curr_val := curr_val - startvalue + 1;
	if curr_val < 0 
	then 
	  string := ' increment by ';
      curr_val:= abs(curr_val); 
	else
	  string := '  increment by -';
 	end if;   
	
	EXECUTE IMMEDIATE 'alter sequence ' ||sequencename||string||curr_val ;
	EXECUTE IMMEDIATE 'SELECT ' ||sequencename ||'.nextval FROM dual' INTO curr_val;
	EXECUTE IMMEDIATE 'alter sequence ' ||sequencename||' increment by 1'; 

END reset_sequence;
/

be carefull using this procedure in a production environment though....

Frans Received on Wed Oct 29 2003 - 03:18:48 CST