Check table exist or not before create it in Oracle

Question

Trying to check is table exist before create in Oracle. Search for most of the post from Stackoverflow and others too. Find some query but it didn't work for me.

IF((SELECT count(*) FROM dba_tables where table_name = 'EMPLOYEE') <= 0) THEN create table EMPLOYEE ( ID NUMBER(3), NAME VARCHAR2(30) NOT NULL ) END IF; 

Which gives me error

Error: ORA-00900: invalid SQL statement SQLState:  42000 ErrorCode: 900 Position: 1 

I search for the syntax for IF condition, I think which is also write. Please suggest me....

Answer 1

As Rene also commented, it's quite uncommon to check first and then create the table. If you want to have a running code according to your method, this will be:

declare nCount NUMBER; v_sql LONG;  begin SELECT count(*) into nCount FROM dba_tables where table_name = 'EMPLOYEE'; IF(nCount <= 0) THEN v_sql:=' create table EMPLOYEE ( ID NUMBER(3), NAME VARCHAR2(30) NOT NULL )'; execute immediate v_sql;  END IF; end; 

But I'd rather go catch on the Exception, saves you some unnecessary lines of code:

declare v_sql LONG; begin  v_sql:='create table EMPLOYEE   (   ID NUMBER(3),   NAME VARCHAR2(30) NOT NULL   )'; execute immediate v_sql;  EXCEPTION     WHEN OTHERS THEN       IF SQLCODE = -955 THEN         NULL; -- suppresses ORA-00955 exception       ELSE          RAISE;       END IF; END;  / 

Answer 2

I know this topic is a bit old, but I think I did something that may be useful for someone, so I'm posting it.

I compiled suggestions from this thread's answers into a procedure:

CREATE OR REPLACE PROCEDURE create_table_if_doesnt_exist(   p_table_name VARCHAR2,   create_table_query VARCHAR2 ) AUTHID CURRENT_USER IS   n NUMBER; BEGIN   SELECT COUNT(*) INTO n FROM user_tables WHERE table_name = UPPER(p_table_name);   IF (n = 0) THEN     EXECUTE IMMEDIATE create_table_query;   END IF; END; 

You can then use it in a following way:

call create_table_if_doesnt_exist('my_table', 'CREATE TABLE my_table (         id NUMBER(19) NOT NULL PRIMARY KEY,         text VARCHAR2(4000),         modified_time TIMESTAMP   )' ); 

I know that it's kinda redundant to pass table name twice, but I think that's the easiest here.

Hope somebody finds above useful :-).