Friday, January 10, 2014

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>

No comments: