title: Oracle SQL "EXECUTE IMMEDIATE" ORA-00922/06512 Create Table
body: <p>i searched a lot and many people got similar problems, but nothing seemed to match.</p>
  
  <p>This is no problem:</p>
  
  <pre><code>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_)
      );
  </code></pre>
  
  <p>Due Oracle lacks of support for "<strong>drop table if exist</strong>" I have to check if the table exists in another way:</p>
  
  <pre><code>  DECLARE cnt NUMBER;
    BEGIN
     SELECT COUNT(*) INTO cnt FROM user_tables WHERE table_name ='CUSTOM_WORKFLOW_ID_COUNTER';
     IF cnt &lt;&gt; 0 THEN
       EXECUTE IMMEDIATE 'DROP TABLE CUSTOM_WORKFLOW_ID_COUNTER';              
     END IF;       
   END;
  </code></pre>
  
  <p>Both works like a charm alone, but If I try to execute both in one SQL statement:</p>
  
  <pre><code>  DECLARE cnt NUMBER;
    BEGIN
    SELECT COUNT(*) INTO cnt FROM user_tables WHERE table_name = 'CUSTOM_WORKFLOW_ID_COUNTER';
      IF cnt &lt;&gt; 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_)
      );
  </code></pre>
  
  <p>I get:</p>
  
  <blockquote>
    <p>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.</p>
  </blockquote>
  
  <p>If I try to execute the create statement inside the block I get another error:</p>
  
  <pre><code>  DECLARE cnt NUMBER;
    BEGIN
     SELECT COUNT(*) INTO cnt FROM user_tables WHERE table_name = 'CUSTOM_WORKFLOW_ID_COUNTER';
    IF cnt &lt;&gt; 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;
  </code></pre>
  
  <p>I get this error:</p>
  
  <blockquote>
    <p>ORA-00922: Fehlende oder ungültige Option
     ORA-06512: in Zeile 18
     00922. 00000 -  "missing or invalid option"</p>
  </blockquote>
  
  <p>What am I doing wrong?</p>
  
  <p>edit:
  After removing the semicolons:</p>
  
  <pre><code>  DECLARE cnt NUMBER;
    BEGIN
     SELECT COUNT(*) INTO cnt FROM user_tables WHERE table_name = 'CUSTOM_WORKFLOW_ID_COUNTER';
   IF cnt &lt;&gt; 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;
  </code></pre>
  
  <p>I get another error ;)</p>
  
  <blockquote>
    <p>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.</p>
  </blockquote>
  
  <p>But this seems to be another problem.</p>