ORA-06550 Why I can't create index inside the if-else in Oracle

Question

I not family with PL/SQL. Can anyone explain why I can't do the following?

BEGIN
  IF TRUE THEN
    CREATE INDEX TestIndex ON SomeTable (SomeColumn);
  END IF;
END;

I would get the following error:

Error report: ORA-06550: line 3, column 5: PLS-00103: Encountered the symbol "CREATE" 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 06550. 00000 - "line %s, column %s:\n%s" *Cause: Usually a PL/SQL compilation error. *Action:

The only way I can by pass this error is do dynamic sql:

BEGIN
  IF TRUE THEN
    EXECUTE IMMEDIATE 'CREATE INDEX TestIndex ON SomeTable (SomeColumn)';
  END IF;
END;

Justin Cave Justin Cave · Accepted Answer · 2011-07-21T19:30:19

Oracle doesn't allow DDL in a PL/SQL block as static SQL so you've identified the only workaround (well, technically, you could use DBMS_SQL rather than EXECUTE IMMEDIATE but you'd still be dealing with dynamic SQL).

I don't know that there is any technical reason that they couldn't allow DDL in static SQL. But since 99% of the time you shouldn't be doing DDL in a stored procedure-- creating objects is something that would almost exclusively be done when you're doing a deployment rather than at runtime-- forcing people to use dynamic SQL makes developers pause to consider whether they're really in that 1% of cases where such a thing makes sense.

ORA-06550 Why I can't create index inside the if-else in Oracle

1 Answers