ORA-06550 when trying to check if a table exists

Question

I'd like to check weather a table exists before creating one, in an Oracle database. Though, the following statement is not working throwing error ORA-06550 on line 7 (CREATE).

  DECLARE cnt NUMBER;
  BEGIN
    SELECT COUNT(*) INTO cnt FROM ALL_TABLES WHERE lower(table_name) = lower('TestTable');

    IF( cnt = 0 )
    THEN
      CREATE TABLE TestTable
      (
        TestFlag NUMBER(1) NOT NULL
      );
    END IF;
  END;

Can anyone help me out with this one?

Thanks in advance!

Answer 1

creating tables on the fly in Oracle is a big no-no, so if this is real code you're running then, stop. use temp tables instead. but the reason for failure is that DDL has to be run in SQL, not PL/SQL.

 DECLARE cnt NUMBER;
  BEGIN
    SELECT COUNT(*) INTO cnt FROM ALL_TABLES WHERE lower(table_name) = lower('TestTable');

    IF( cnt = 0 )
    THEN
      execute immediate 'CREATE TABLE TestTable (testFlag NUMBER(1) NOT NULL)';
    END IF;
  END;