Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.misc -> Re: 'alter sequence' in a PL/SQL procedure?! (!@#$%^&*)
You can't do DDL using static sql, you must do DDL using dynamic sql. Once you install the following 'execute_immediate' routine, you'll be able to:
drop sequence fooseq;
create sequence fooseq start with 10;
create or replace procedure alter_fooseq as
n number;
begin
n := execute_immediate( 'alter sequence fooseq minvalue 5 cycle' );
end;
/
exec alter_fooseq
create or replace function execute_immediate( stmt in varchar2 ) return number as exec_cursor integer default dbms_sql.open_cursor; rows_processed number default 0; begin dbms_sql.parse(exec_cursor, stmt, dbms_sql.native ); rows_processed := dbms_sql.execute(exec_cursor); dbms_sql.close_cursor( exec_cursor ); return rows_processed; exception when others then if dbms_sql.is_open(exec_cursor) then dbms_sql.close_cursor(exec_cursor); end if; raise; end; /
On Mon, 14 Jul 1997 17:10:25 -0700, Kenn Herman <kennh_at_sierra.com> 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?
>
>Kenn Herman
>Assistant Technical Webmaster
>Sierra On-Line
>----------------------------------------------------------------------
>When kids where asked about love....
>"Don't do things like have smelly, green sneakers. You might get
> attention, but attention ain't the same thing as love."
>
> Alonzo, age 9
>----------------------------------------------------------------------
>
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD
http://govt.us.oracle.com/ -- downloadable utilities
![]() |
![]() |