sql - Oracle SQL "EXECUTE IMMEDIATE"ORA-00922/06512 创建表

Question

我搜索了很多，很多人都遇到了类似的问题，但似乎没有匹配的。

这没问题:

create table CUSTOM_WORKFLOW_ID_COUNTER (
    id_ NUMBER (19) GENERATED by default on null as IDENTITY,
    type_ VARCHAR2(50 CHAR),
    masterId_ VARCHAR2(100 CHAR),
    counter_ NUMBER (19),
    primary key (id_)
    );

由于 Oracle 缺乏对“如果存在则删除表”的支持，我必须以另一种方式检查该表是否存在:

  DECLARE cnt NUMBER;
  BEGIN
   SELECT COUNT(*) INTO cnt FROM user_tables WHERE table_name ='CUSTOM_WORKFLOW_ID_COUNTER';
   IF cnt <> 0 THEN
     EXECUTE IMMEDIATE 'DROP TABLE CUSTOM_WORKFLOW_ID_COUNTER';              
   END IF;       
 END;

两者单独工作就像一个魅力，但如果我尝试在一个 SQL 语句中执行两者:

  DECLARE cnt NUMBER;
  BEGIN
  SELECT COUNT(*) INTO cnt FROM user_tables WHERE table_name = 'CUSTOM_WORKFLOW_ID_COUNTER';
    IF cnt <> 0 THEN
      EXECUTE IMMEDIATE 'DROP TABLE CUSTOM_WORKFLOW_ID_COUNTER';              
    END IF;       
  END;
 create table CUSTOM_WORKFLOW_ID_COUNTER (
    id_ NUMBER (19) GENERATED by default on null as IDENTITY,
    type_ VARCHAR2(50 CHAR),
    masterId_ VARCHAR2(100 CHAR),
    counter_ NUMBER (19),
    primary key (id_)
    );

我得到:

ORA-06550: Zeile 9, Spalte 1: PLS-00103: Fand das Symbol "CREATE" 06550. 00000 - "line %s, column %s:\n%s" *Cause: Usually a PL/SQL compilation error.

如果我尝试在 block 内执行 create 语句，我会收到另一个错误:

  DECLARE cnt NUMBER;
  BEGIN
   SELECT COUNT(*) INTO cnt FROM user_tables WHERE table_name = 'CUSTOM_WORKFLOW_ID_COUNTER';
  IF cnt <> 0 THEN
   EXECUTE IMMEDIATE 'DROP TABLE CUSTOM_WORKFLOW_ID_COUNTER';
   EXECUTE IMMEDIATE 'create table CUSTOM_WORKFLOW_ID_COUNTER (
    id_ NUMBER (19) GENERATED by default on null as IDENTITY,
    type_ VARCHAR2(50 CHAR),
    masterId_ VARCHAR2(100 CHAR),
    counter_ NUMBER (19),
    primary key (id_)
    );';
 ELSE
    EXECUTE IMMEDIATE 'create table CUSTOM_WORKFLOW_ID_COUNTER (
    id_ NUMBER (19) GENERATED by default on null as IDENTITY,
    type_ VARCHAR2(50 CHAR),
    masterId_ VARCHAR2(100 CHAR),
    counter_ NUMBER (19),
    primary key (id_)
    );';      
  END IF;
END;

我收到此错误:

ORA-00922: Fehlende oder ungültige Option ORA-06512: in Zeile 18 00922. 00000 - "missing or invalid option"

我做错了什么？

编辑:删除分号后:

  DECLARE cnt NUMBER;
  BEGIN
   SELECT COUNT(*) INTO cnt FROM user_tables WHERE table_name = 'CUSTOM_WORKFLOW_ID_COUNTER';
 IF cnt <> 0 THEN
  EXECUTE IMMEDIATE 'DROP TABLE CUSTOM_WORKFLOW_ID_COUNTER';
  EXECUTE IMMEDIATE 'create table CUSTOM_WORKFLOW_ID_COUNTER (
id_ NUMBER (19) GENERATED by default on null as IDENTITY,
type_ VARCHAR2(50 CHAR),
masterId_ VARCHAR2(100 CHAR),
counter_ NUMBER (19),
primary key (id_)
)';
ELSE
EXECUTE IMMEDIATE 'create table CUSTOM_WORKFLOW_ID_COUNTER (
id_ NUMBER (19) GENERATED by default on null as IDENTITY,
type_ VARCHAR2(50 CHAR),
masterId_ VARCHAR2(100 CHAR),
counter_ NUMBER (19),
primary key (id_)
)';      
 END IF;
END;

我收到另一个错误;)

ORA-00054: Ressource belegt und Anforderung mit NOWAIT angegeben oder Timeout abgelaufen ORA-06512: in Zeile 5 00054. 00000 - "resource busy and acquire with NOWAIT specified or timeout expired" *Cause: Interested resource is busy. *Action: Retry if necessary or increase timeout.

但这似乎是另一个问题。

Answer 1

问题是由于黄色分号造成的(第 12 行和第 20 行):

EXECUTE IMMEDIATE 'create table CUSTOM_WORKFLOW_ID_COUNTER (
id_ NUMBER (19) GENERATED by default on null as IDENTITY,
type_ VARCHAR2(50 CHAR),
masterId_ VARCHAR2(100 CHAR),
counter_ NUMBER (19),
primary key (id_)
)

;

'