Oracle: If Table Exists

Oracle: If Table Exists

The best and most efficient way is to catch the "table not found" exception: this avoids the overhead of checking if the table exists twice; and doesn't suffer from the problem that if the DROP fails for some other reason (that might be important) the exception is still raised to the caller:

BEGIN
   EXECUTE IMMEDIATE 'DROP TABLE ' || table_name;
EXCEPTION
   WHEN OTHERS THEN
      IF SQLCODE != -942 THEN
         RAISE;
      END IF;
END;

ADDENDUM
For reference, here are the equivalent blocks for other object types:

Sequence

BEGIN
  EXECUTE IMMEDIATE 'DROP SEQUENCE ' || sequence_name;
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE != -2289 THEN
      RAISE;
    END IF;
END;

View

BEGIN
  EXECUTE IMMEDIATE 'DROP VIEW ' || view_name;
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE != -942 THEN
      RAISE;
    END IF;
END;

Trigger

BEGIN
  EXECUTE IMMEDIATE 'DROP TRIGGER ' || trigger_name;
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE != -4080 THEN
      RAISE;
    END IF;
END;

Index

BEGIN
  EXECUTE IMMEDIATE 'DROP INDEX ' || index_name;
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE != -1418 THEN
      RAISE;
    END IF;
END;

Column

BEGIN
  EXECUTE IMMEDIATE 'ALTER TABLE ' || table_name
                || ' DROP COLUMN ' || column_name;
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE != -904 AND SQLCODE != -942 THEN
      RAISE;
    END IF;
END;

Database Link

BEGIN
  EXECUTE IMMEDIATE 'DROP DATABASE LINK ' || dblink_name;
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE != -2024 THEN
      RAISE;
    END IF;
END;

Materialized View

BEGIN
  EXECUTE IMMEDIATE 'DROP MATERIALIZED VIEW ' || mview_name;
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE != -12003 THEN
      RAISE;
    END IF;
END;

Type

BEGIN
  EXECUTE IMMEDIATE 'DROP TYPE ' || type_name;
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE != -4043 THEN
      RAISE;
    END IF;
END;

Constraint

BEGIN
  EXECUTE IMMEDIATE 'ALTER TABLE ' || table_name
            || ' DROP CONSTRAINT ' || constraint_name;
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE != -2443 AND SQLCODE != -942 THEN
      RAISE;
    END IF;
END;

Scheduler Job

BEGIN
  DBMS_SCHEDULER.drop_job(job_name);
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE != -27475 THEN
      RAISE;
    END IF;
END;

User / Schema

BEGIN
  EXECUTE IMMEDIATE 'DROP USER ' || user_name;
  /* you may or may not want to add CASCADE */
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE != -1918 THEN
      RAISE;
    END IF;
END;

Package

BEGIN
  EXECUTE IMMEDIATE 'DROP PACKAGE ' || package_name;
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE != -4043 THEN
      RAISE;
    END IF;
END;

Procedure

BEGIN
  EXECUTE IMMEDIATE 'DROP PROCEDURE ' || procedure_name;
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE != -4043 THEN
      RAISE;
    END IF;
END;

Function

BEGIN
  EXECUTE IMMEDIATE 'DROP FUNCTION ' || function_name;
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE != -4043 THEN
      RAISE;
    END IF;
END;

Tablespace

BEGIN
  EXECUTE IMMEDIATE 'DROP TABLESPACE ' || tablespace_name;
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE != -959 THEN
      RAISE;
    END IF;
END;

Synonym

BEGIN
  EXECUTE IMMEDIATE 'DROP SYNONYM ' || synonym_name;
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE != -1434 THEN
      RAISE;
    END IF;
END;

Using SQL query to determine if a table exists

You can do this (in oracle, in mssql there is a bit different):

select count(*)
from all_objects
where object_type in ('TABLE','VIEW')
and object_name = 'your_table_name';

Check table exist or not before create it in Oracle

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

How can drop table if table exists in oracle?

If you run following code you do not have to check if table exists and in case of errors (table is locked with now wait or any other you will know about it)

begin
    for c1 in (select owner,table_name from dba_tables where table_name='MY_TABLE') loop
        execute immediate 'drop table '||c1.owner||'.'||c1.table_name||'';
    end loop;
end;

Check if table exists in the database - PL SQL

you can query the tablenames

select tname from tab where tname = 'TABLE_NAME_TO_SEARCH_FOR';

Drop table from oracle database if table exist in sql statement

Oracle does not support a construct like drop table if exists my_table, which is apparently legal syntax in MySQL (and possibly other RDBMSs).

In a .SQL script, where you're running DDL to DROP and/or CREATE various objects, the Oracle standard is to drop the object, and ignore the error in cases where the object does not exist. If you wish, you can write code to check if the object exists (see DBA_OBJECTS view) to only drop if it exists.

from the s.executeUpdate, I gather that you're doing this in Java? If it was me, I'd just do the drop and ignore any not exists error.

Oracle SQL - If Exists, Drop Table & Create

The EXCEPTION clause lasts till the next END and not just the next statement. If you want to continue after catching the exception you need to add an additional BEGIN/END:

declare 
    Table_exists INTEGER; 
BEGIN 
    BEGIN
        Select count(*) into Table_exists from sys.all_tables where table_name='TABLENAME1'; 
    EXCEPTION 
    WHEN NO_DATA_FOUND THEN 
        Table_Exists :=0; 
    END;

    if(table_exists)=1 Then 
        Execute Immediate 'Drop Table TABLENAME1;'     
        Execute Immediate 'Create Table TABLENAME1;'; 
        DBMS_OUTPUT.PUT_LINE('Table Dropped and Re-Created!'); 
    Else 
        Execute Immediate 'Create Table TABLENAME1;'; 
        DBMS_OUTPUT.PUT_LINE('New Table Created!'); 
    END IF; 
END;

As pointed out by Gordon, the EXCEPTION clause is not really needed in this case since count(*) will always return one row. So the following is sufficient:

declare 
    Table_exists INTEGER; 
BEGIN 
    Select count(*) into Table_exists from sys.all_tables where table_name='TABLENAME1'; 

    if(table_exists)=1 Then 
        Execute Immediate 'Drop Table TABLENAME1;'     
        Execute Immediate 'Create Table TABLENAME1;'; 
        DBMS_OUTPUT.PUT_LINE('Table Dropped and Re-Created!'); 
    Else 
        Execute Immediate 'Create Table TABLENAME1;'; 
        DBMS_OUTPUT.PUT_LINE('New Table Created!'); 
    END IF; 
END;

---

Related Topics