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

Jeff Kish 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

It is a bad idea even when done with good syntax.

You must use dynamic SQL to execute DDL.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

On Feb 12, 8:03 pm, Jeff Kish <jeff.k...@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.