Re: how to refresh a sequence

From: Rauf Sarwar <rs_arwar_at_hotmail.com>
Date: 23 Feb 2005 01:49:06 -0800
Message-ID: <1109152146.114454.195510@z14g2000cwz.googlegroups.com>

Robert Wehofer wrote:
> How could I refresh a sequence in Oracle using an SQL statement?
>
> I've got the problem, that the current number of a sequence is less
the
> highest index in a table, where I want to use the sequence.
>
> Regards,
> Robert

Either drop and recreate OR

declare

    max_value_     number := <max value to increment to>;
    currval_       number;
    nextval_       number;

begin

    loop

       select your_seq.currval into currval_ from dual;
       if (currval_ >= max_value_) then
          exit;
       else
          select your_seq.nextval into nextval_ from dual;
       end if;

    end loop;
end;
/

Regards
/Rauf Received on Wed Feb 23 2005 - 03:49:06 CST