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

For an existing table's i needed this kind of solution and after some gathering, i did this kind of way: check existence of tables and sequences from SYS.ALL_OBJECTS and add if necessary(i assumed connected user has/has to access to this)

DECLARE
  table_exists NUMBER;
  sequence_exists NUMBER;
  createTableSql clob;
  createSequenceSql clob;
BEGIN
  createTableSql:='
CREATE TABLE myTable(....)
';
  createSequenceSql := '
CREATE SEQUENCE .....
';


SELECT COUNT(*)
INTO table_exists
FROM SYS.ALL_OBJECTS O
WHERE O.OWNER = 'My_Schema' -- your schema name, i needed it
  AND O.OBJECT_TYPE = 'TABLE'
  AND O.OBJECT_NAME = 'MyTable'; -- your table name

SELECT COUNT(*)
INTO sequence_exists
FROM SYS.ALL_OBJECTS O
WHERE O.OWNER = 'My_Schema'  -- your schema name, i needed it
  AND O.OBJECT_TYPE = 'SEQUENCE'
  AND O.OBJECT_NAME = 'My_SchemaName';  -- your sequence name


IF
table_exists = 0 THEN
    DBMS_OUTPUT.PUT_LINE('table not found(object count: '||table_exists||') adding new...');
execute immediate createTableSql;
ELSE
    DBMS_OUTPUT.PUT_LINE('table found(object count:'|| table_exists||') skipping...' );
END IF;


IF
sequence_exists = 0 THEN
    DBMS_OUTPUT.PUT_LINE('sequence not found(object count: '||sequence_exists ||') adding new...');
execute immediate createSequenceSql ;
ELSE
    DBMS_OUTPUT.PUT_LINE('sequence found(object count:'|| sequence_exists ||') skipping...' );
END IF;
END;

Be careful when creating scripts inside variables, problems occured when i

• left ; at the end
• multi creating statements in one variable I have to check the DBMS output to verify them

Have a nice day!

Answer 2

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

Answer 3

Its no need declare and count apply too.

begin
for rec in (select 1 from user_tables where table_name = 'YOUR_TABLE')
-- or
-- for rec in (select 1 from all_tables where table_name = 'YOUR_TABLE' and owner = 'YOU')
loop
    execute immediate 'create table your_table as (f1 char(1))';
end loop;
end;
/

Will be good mode create check function

create or replace function this_object_exists (p_obj_name user_objects.object_name%type) return boolean
is
begin
   for rec in (select 1 from user_objects where object_name = upper(p_obj_name))
   loop
        return true;
   end loop;
   return false;
end this_object_exists;

And thus use code for check exists

.	.	.	.
INDEX PARTITION	TABLE SUBPARTITION	SEQUENCE	TABLE PARTITION
PROCEDURE	LOB PARTITION	LOB	INDEX SUBPARTITION
PACKAGE	PACKAGE BODY	TYPE BODY	TRIGGER
INDEX	TABLE	VIEW	FUNCTION
SYNONYM	TYPE	JOB	...

begin
if not this_object_exists('your_table') then
    execute immediate 'create table your_table as (f1 char(1))';
end if;
end;

or

begin
if this_object_exists('your_table') then
    execute immediate 'drop table your_table';
end if;
execute immediate 'create table your_table as (f1 char(1))';
end;

Answer 4

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.

Answer 5

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

Answer 6

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

Answer 7

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.

Answer 8

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

Answer 9

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;

Answer 10

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;