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

User · Answer

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.

User · Answer

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

User · Answer

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 lt  0 then      EXECUTE IMMEDIATE  create table EMPLOYEE   ID NUMBER 3   NAME VARCHAR2 30  NOT NULL      end if  END

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  -

User · Answer

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

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

955 is failure code.

Examples related to oracle

Examples related to if-statement