PLS-00201：使用立即执行时必须声明标识符"立即"？

Question

我正在尝试使用execute immediate创建一个表，如下所示：

 EXECUTE IMMEDIATE (q'{CREATE TABLE ... ;}');

然而，oracle给了我以下错误：

ORA-06550: line 1, column 7:
PLS-00201: identifier 'IMMEDIATE' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

如何正确格式化字符串以创建表？

Answer 1

不能使用sqlplus使用动态sql运行ddl操作。

复制错误的测试用例

   SQL> EXECUTE IMMEDIATE (q'{CREATE TABLE test1000(id int,name varchar2(10)}');
    BEGIN IMMEDIATE (q'{CREATE TABLE test1000(id int,name varchar2(10)}';); END;
    
          *
    ERROR at line 1:
    ORA-06550: line 1, column 7:
    PLS-00201: identifier 'IMMEDIATE' must be declared
    ORA-06550: line 1, column 7:
    PL/SQL: Statement ignored

由于它在sqlplus中不起作用，让我们切换到匿名pl/sql块

  SQL> BEGIN
  2   EXECUTE IMMEDIATE (q'{CREATE TABLE test1000(id int,name varchar2(10));}');
  3* END;
SQL> /
BEGIN
*
ERROR at line 1:
ORA-00922: missing or invalid option
ORA-06512: at line 2

删除大括号中的冒号以避免出现上述错误

SQL> BEGIN
  2   EXECUTE IMMEDIATE (q'{CREATE TABLE test1000(id int,name varchar2(10))}');
  3* END;
PL/SQL procedure successfully completed.
SQL> @ddl test1000
PL/SQL procedure successfully completed.

DBMS_METADATA.GET_DDL(OBJECT_TYPE,OBJECT_NAME,OWNER)
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  CREATE TABLE "HR"."TEST1000"
   (    "ID" NUMBER(*,0),
        "NAME" VARCHAR2(10) COLLATE "USING_NLS_COMP"
   )  DEFAULT COLLATION "USING_NLS_COMP" SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  TABLESPACE "EXAMPLE" ;