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 clob;
begin
  select count(*) into nCount from dba_tables where table_name = 'EMPLOYEE';

  if ncount <= 0 then
    v_sql := '
      create table employee
      (
        id number,
        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 clob;
begin
  v_sql := '
    create table employee
    (
      id number,
      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; 
/

Since Oracle 23c, you can use the simpler IF NOT EXISTS syntax:

create table if not exists employee
(
  id number,
  name varchar2(30) not null
);