32

I create a test.sql file and inside I put:

begin
alter table table1 enable row movement;
alter table table1 shrink space;
end;
/

Is this not allowed? Because I get error:

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 <an identifier>
   <a double-quoted delimited-identifier> <a bind variable> <<
   close current delete fetch lock insert open rollback
   savepoint set sql execute commit forall merge pipe
Ollie
  • 17,058
  • 7
  • 48
  • 59
Victor
  • 16,609
  • 71
  • 229
  • 409

1 Answers1

57

You cannot issue DDL as static SQL in a PL/SQL block. If you want to put those commands in a PL/SQL block, you'd need to use dynamic SQL, i.e.

BEGIN
  EXECUTE IMMEDIATE 'alter table table1 enable row movement';
  EXECUTE IMMEDIATE 'alter table table1 shrink space cascade';
END;
/

It may be easier, however, to just issue consecutive SQL statements rather than issuing a single PL/SQL block.

Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • Thank you again. There are too many statements to execute independently :) – Victor Sep 27 '11 at 21:40
  • An old answer, but a very useful one. However, I wonder _why_ you can't issue straight DDL statements like this. – osullic May 18 '23 at 16:09
  • 1
    @osullic - In general, the issue is that Oracle has to compile the entire PL/SQL block before it can be executed. If DDL was generally allowed, the compiler would have to try to figure out whether DML statements would be syntactically valid after the DDL statement completed which, in general, is extremely hard. Dynamic SQL isn't checked at compile time but then it's up to the author to ensure that code behaves appropriately (i.e. DDL locks would be taken on `table1` if there were static references to the table in the PL/SQL block which would block the `alter table` command). – Justin Cave May 18 '23 at 16:40