PL/SQL块中如何调用DDL语句

 

在PL/SQL块中不能直接调用DDL语句，可以利用下面的方法进行调用

方法一：动态SQL

execute immediate 'CREATE TABLE newtable AS
                                                                 SELECT *
                                                                    FROM table_my
                                                                  WHERE 1 = 2; ';

方法二：

可以利用ORACLE内置包DBMS_UTILITY中的EXEC_DDL_STATEMENT过程，由它执行DDL语句。

BEGIN

       -- 创建用于记录事件日志的数据表

       DBMS_UTILITY.EXEC_DDL_STATEMENT('

              CREATE TABLE eventlog(

                     Eventname VARCHAR2(20) NOT NULL,

                     Eventdate date default sysdate,

                     Inst_num NUMBER NULL,

                     Db_name VARCHAR2(50) NULL,

                     Srv_error NUMBER NULL,

                     Username VARCHAR2(30) NULL,

                     Obj_type VARCHAR2(20) NULL,

                     Obj_name VARCHAR2(30) NULL,

                     Obj_owner VARCHAR2(30) NULL

              )

       ');