[oracle] Check table exist or not before create it in Oracle

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....

declare n number(10);

begin
   select count(*) into n from tab where tname='TEST';

   if (n = 0) then 
      execute immediate 
      'create table TEST ( ID NUMBER(3), NAME VARCHAR2 (30) NOT NULL)';
   end if;
end;

Please try:

SET SERVEROUTPUT ON
DECLARE
v_emp int:=0;
BEGIN
  SELECT count(*) into v_emp FROM dba_tables where table_name = 'EMPLOYEE'; 

  if v_emp<=0 then
     EXECUTE IMMEDIATE 'create table EMPLOYEE ( ID NUMBER(3), NAME VARCHAR2(30) NOT NULL)';
  end if;
END;

My solution is just compilation of best ideas in thread, with a little improvement. I use both dedicated procedure (@Tomasz Borowiec) to facilitate reuse, and exception handling (@Tobias Twardon) to reduce code and to get rid of redundant table name in procedure.

DECLARE

    PROCEDURE create_table_if_doesnt_exist(
        p_create_table_query VARCHAR2
    ) IS
    BEGIN
        EXECUTE IMMEDIATE p_create_table_query;
    EXCEPTION
        WHEN OTHERS THEN
        -- suppresses "name is already being used" exception
        IF SQLCODE = -955 THEN
            NULL; 
        END IF;
    END;

BEGIN
    create_table_if_doesnt_exist('
        CREATE TABLE "MY_TABLE" (
            "ID" NUMBER(19) NOT NULL PRIMARY KEY,
            "TEXT" VARCHAR2(4000),
            "MOD_TIME" TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    ');
END;
/

-- checks for table in specfic schema:

declare n number(10);

begin
    Select count(*) into n  from SYS.All_All_Tables where owner = 'MYSCHEMA' and TABLE_NAME =  'EMPLOYEE';

   if (n = 0) then 
     execute immediate 
     'create table MYSCHEMA.EMPLOYEE ( ID NUMBER(3), NAME VARCHAR2(30) NOT NULL)';      
   end if;
end;

Well there are lot of answeres already provided and lot are making sense too.

Some mentioned it is just warning and some giving a temp way to disable warnings. All that will work but add risk when number of transactions in your DB is high.

I came across similar situation today and here is very simple query I came up with...

declare
begin
  execute immediate '
    create table "TBL" ("ID" number not null)';
  exception when others then
    if SQLCODE = -955 then null; else raise; end if;
end;
/

955 is failure code.

This is simple, if exception come while running query it will be suppressed. and you can use same for SQL or Oracle.

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 :-).

Any solution which relies on testing before creation can run into a 'race' condition where another process creates the table between you testing that it does not exists and creating it. - Minor point I know.