Re: 'alter sequence' in a PL/SQL procedure?! (!@#$%^&*)

Kenn Herman wrote:
>
> I have been trying to have a procedure alter a sequence in a procedure,
> but to no avail. I receive the following error:
>
> LINE/COL ERROR
> -------- -----------------------------------------------------------------
> 4/7 PLS-00103: Encountered the symbol "ALTER" when expecting one of
> the following:
> begin declare exit for goto if loop mod null pragma raise
> return select update while <an identifier>
> <a double-quoted delimited-identifier> etc.
>
> When i try and do the following:
>
> create or replace procedure restart_seq (start_val in number)
> as
> begin
> alter sequence the_seq minvalue start_val cycle;
> end;
>
> Why will PL/SQL not allow me to alter a sequence? To test it I ran it on
> the sqlplus command line, and also tried to do a drop and then recreate
> the sequence, but it barfed on the DROP.
>
> Any suggestions?
>

You need to use DBMS_SQL. Check the manuals ("utilities guide" I think) for details. There's also a file in $ORACLE_HOME/rdbms/admin/dbmssql.sql that describes the package and comes with examples as well. Essentially you can't use DDL in packages/procedures/functions yet. You must do it thru dynamic sql.

Leng. Received on Tue Jul 15 1997 - 00:00:00 CDT