How to alter table using cursor variable in execute immediate statement

Question

I have 7 tables in PS server, who have data from last 3 years(Billions of records). Requirement is to move all the data to a back up table except the last months data. So we approach:

1) to change the existing table name as back up table
2) and then create new table
3) and then move the 3 months data to new table.

This way data processing will be fast.

I tried with below query to change the table name but its not worked. Can any one help. It's high priority.

declare 
 sql_stmt varchar2(1000); 
 cursor c1 is select table_name from staging_clear; 
 type t1 is table of c1%rowtype; 
 curtype t1;
 begin 
 open c1; 
 fetch c1 bulk collect into curtype;  
 for i in 1..curtype.count loop  
 sql_stmt:= 'begin 
          alter table  '||curtype(i).table_name||' rename to '|| curtype(i).table_name||'_bkp';
          dbms_output.put_line(sql_stmt);
   execute immediate sql_stmt; 
   dbms_output.put_line(sql_stmt); 
  end loop;  
 end;

Error report:

ORA-06550: line 2, column 11: PLS-00103: Encountered the symbol "ALTER" when expecting one of the following:begin case declare exit for goto if loop mod null pragma raise return select update while with << close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe ORA-06512: at line 13 06550. 00000 - "line %s, column %s:\n%s" *Cause: Usually a PL/SQL compilation error.

TonZ TonZ · Accepted Answer · 2013-11-17T13:43:48

the statement is broken: just write 'ALTER TABLE' and forget about the 'begin'

sql_stmt:= 'alter table  '||curtype(i).table_name||' rename to '|| curtype(i).table_name||'_bkp';

How to alter table using cursor variable in execute immediate statement

1 Answers