Wednesday, January 29, 2014

DBMS_UTILITY.EXEC_DDL_STATEMENT

I published a post recently about executing DDL from PL/SQL. You can also do this using dbms_utility.exec_ddl_statement as shown in the examples below, which I tested in Oracle 11: 

SQL> begin
  2    dbms_utility.exec_ddl_statement
  3    ('create table tab1(col1 number)');
  4  end;
  5  /
 
PL/SQL procedure successfully completed.
 
SQL> declare
  2    sql_statement varchar2(50);
  3  begin
  4    sql_statement := 'alter table tab1 add(col2 number)';
  5    dbms_utility.exec_ddl_statement(sql_statement);
  6  end;
  7  /
 
PL/SQL procedure successfully completed.
 
SQL> desc tab1
Name                       Null?    Type
-------------------------- -------- ------------------
COL1                                NUMBER
COL2                                NUMBER
 
SQL> 

It even seems to allow you to run DDL in a remote database: 

SQL> begin
  2    dbms_utility.exec_ddl_statement@remote_db
  3    ('create table tab1(col1 number)');
  4  end;
  5  /
 
PL/SQL procedure successfully completed.
 
SQL> desc tab1@remote_db
Name                       Null?    Type
-------------------------- -------- ------------------
COL1                                NUMBER
 
SQL>

No comments: