On Feb 12, 8:03 pm, Jeff Kish <jeff.k..._at_mro.com> wrote:
> Hi.
>
> I can't seem to figure out the correct syntax here. This should
> be usable on oracle 9i up.
>
> I have a sequence and table that is a bit messed up and I'd like
> to supply an sql script someone could run to fix things.
> Bacially
>
> declare
> myMax number;
> begin
> select max (table_id) + 1 into somevar from mytable into myMax;
> drop sequence mytable_table_id_seq;
> create sequence mytable_table_id_seq startwith myMax;
>
> end;
>
> but it does not like the drop and create.
>
> Can someone tell me the best way to get this working?
>
> thanks
> Jeff Kish
Rather than dropping and recreating, you should adjust the increment,
get sequence.nextval, and set the increment back to what it was. This
is a bit trickier but avoids invalidating dependant code and losing
grants etc.
Received on Tue Feb 13 2007 - 03:46:58 CST
