Can not have CREATE TABLE inside if else

Question

When I run this query

DECLARE
      num NUMBER;
BEGIN

    SELECT COUNT(*) INTO num FROM user_all_tables WHERE TABLE_NAME=upper('DatabaseScriptLog')
    ;

    IF num < 1 THEN

      CREATE TABLE DatabaseScriptLog 
      (ScriptIdentifier VARCHAR(100) NOT NULL,
       ScriptType VARCHAR(50), 
       StartDate TIMESTAMP, 
       EndDate TIMESTAMP, 
       PRIMARY KEY (ScriptIdentifier)
       );

    END IF;

END;

When execute the above, I got the following:

PLS-00103: Encountered the symbol "CREATE" when expecting one of the following:

begin case declare exit for goto if loop mod null pragma raise return select update while with << close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge pipe 06550. 00000 - "line %s, column %s:\n%s" *Cause: Usually a PL/SQL compilation error.

What is your question? "What does this error mean?", "Does this mean I can't create a table inside a conditional?", "How should I do this instead?", etc. Thanks! — Devin Burke

Thilo Thilo · Accepted Answer · 2011-06-08T00:35:10

You cannot run DDL statements like that. You need to use dynamic SQL (EXECUTE IMMEDIATE).

IF num < 1 THEN

  EXECUTE IMMEDIATE 'CREATE TABLE DatabaseScriptLog (ScriptIdentifier VARCHAR(100) NOT NULL, ScriptType VARCHAR(50), StartDate TIMESTAMP, EndDate TIMESTAMP, PRIMARY KEY (ScriptIdentifier))'

END IF;

Can not have CREATE TABLE inside if else

2 Answers