Re: managing sequences

Hi

Thanks very much for your help. It will be very useful for my project.

Morgan

"Frans H." <fransh_at_hotmail.com> a écrit dans le message de news: 31abf0e5.0310290118.52c3e6f2_at_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 Thu Oct 30 2003 - 05:08:31 CST