Re: drop and recreate sequence from sql

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_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org