Oracle SQL "EXECUTE IMMEDIATE" ORA-00922/06512 Create Table

Question

i searched a lot and many people got similar problems, but nothing seemed to match.

This is no problem:

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_)
    );

Due Oracle lacks of support for "drop table if exist" I have to check if the table exists in another way:

  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;

Both works like a charm alone, but If I try to execute both in one SQL statement:

  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_)
    );

I get:

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.

If I try to execute the create statement inside the block I get another error:

  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;

I get this error:

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

What am I doing wrong?

edit: After removing the semicolons:

  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;

I get another error ;)

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.

But this seems to be another problem.

Answer 1

Just remove the semicolon at the end of the create table:

  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;

Answer 2

problem is due to semicolon in yellow ( at line 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_)
)

;

'