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

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1997/07/15
Message-ID: <33cbdc10.86954523@newshost>#1/1

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

• execute_immediate ------------------

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

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Tue Jul 15 1997 - 00:00:00 CDT