How to Run DDL in PL/SQL

This was tested on Oracle 11.2. You cannot run DDL directly in PL/SQL. If you try, you get an error:

 

SQL> conn / as sysdba

Connected.

SQL> select account_status

  2    from dba_users

  3    where username = 'ANDREW'

  4  /

 

ACCOUNT_STATUS

--------------------------------

OPEN

 

SQL> begin

  2    alter user andrew account lock;

  3  end;

  4  /

  alter user andrew account lock;

  *

ERROR at line 2:

ORA-06550: line 2, column 3:

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 <an identifier>

<a double-quoted delimited-identifier> <a bind

variable> <<

continue close current delete fetch lock insert open

rollback

savepoint set sql execute commit forall merge pipe

purge

 

SQL>

 

In Oracle 7, you could get round this by using DBMS_SQL something like this:

 

SQL> declare

  2    cid integer;

  3  begin

  4    cid := dbms_sql.open_cursor;

  5    dbms_sql.parse(cid,

  6      'alter user andrew account lock', dbms_sql.v7);

  7    dbms_sql.close_cursor(cid);

  8  end;

  9  /

 

PL/SQL procedure successfully completed.

 

SQL> select account_status

  2    from dba_users

  3    where username = 'ANDREW'

  4  /

 

ACCOUNT_STATUS

--------------------------------

LOCKED

 

SQL>

 

In version 8, Oracle introduced the EXECUTE IMMEDIATE command, which is much easier to use:

 

SQL> begin

  2    execute immediate 'alter user andrew account unlock';

  3  end;

  4  /

 

PL/SQL procedure successfully completed.

 

SQL> select account_status

  2    from dba_users

  3    where username = 'ANDREW'

  4  /

 

ACCOUNT_STATUS

--------------------------------

OPEN

 

SQL>