Oracle If Table Exists

Question

I m writing some migration scripts for an Oracle database  and was hoping Oracle had something similar to MySQL s IF EXISTS construct   Specifically  whenever I want to drop a table in MySQL  I do something like  DROP TABLE IF EXISTS  table name     This way  if the table doesn t exist  the DROP doesn t produce an error  and the script can continue   Does Oracle have a similar mechanism   I realize I could use the following query to check if a table exists or not  SELECT   FROM dba tables where table name    table name     but the syntax for tying that together with a DROP is escaping me

User · Answer

I prefer to specify the table and the schema owner.

Watch out for case sensitivity as well. (see "upper" clause below).

I threw a couple of different objects in to show that is can be used in places besides TABLEs.

.............

declare
   v_counter int;
begin
 select count(*) into v_counter from dba_users where upper(username)=upper('UserSchema01');
   if v_counter > 0 then
      execute immediate 'DROP USER UserSchema01 CASCADE';
   end if; 
end;
/



CREATE USER UserSchema01 IDENTIFIED BY pa$$word
  DEFAULT TABLESPACE users
  TEMPORARY TABLESPACE temp
  QUOTA UNLIMITED ON users;

grant create session to UserSchema01;

And a TABLE example:

declare
   v_counter int;
begin
 select count(*) into v_counter from all_tables where upper(TABLE_NAME)=upper('ORDERS') and upper(OWNER)=upper('UserSchema01');
   if v_counter > 0 then
      execute immediate 'DROP TABLE UserSchema01.ORDERS';
   end if; 
end;
/

User · Answer

And if you want to make it re-enterable and minimize drop create cycles  you could cache the DDL using dbms metadata get ddl and re-create everything using a construct like this         declare        v ddl varchar2 4000         begin        select dbms metadata get ddl  TABLE   DEPT   SCOTT   into v ddl from dual          COMPARE CACHED DDL AND EXECUTE IF NO MATCH        exception when others then        if sqlcode   -31603 then          GET AND EXECUTE CACHED DDL         else         raise         end if        end   This is just a sample  there should be a loop inside with DDL type  name and owner being variables

User · Answer

Sadly no  there is no such thing as drop if exists  or CREATE IF NOT EXIST  You could write a plsql script to include the logic there   http   download oracle com docs cd B12037 01 server 101 b10759 statements 9003 htm  I m not much into Oracle Syntax  but i think  Erich s script would be something like this   declare  cant integer begin select into cant count    from dba tables where table name  Table name   if count gt 0 then BEGIN     DROP TABLE tableName  END IF  END

User · Answer

I have been looking for the same but I ended up writing a procedure to help me out   CREATE OR REPLACE PROCEDURE DelObject ObjName varchar2 ObjType varchar2  IS  v counter number    0     begin       if ObjType    TABLE  then     select count    into v counter from user tables where table name   upper ObjName       if v counter  gt  0 then                 execute immediate  drop table      ObjName      cascade constraints               end if       end if    if ObjType    PROCEDURE  then     select count    into v counter from User Objects where object type    PROCEDURE  and OBJECT NAME   upper ObjName         if v counter  gt  0 then                   execute immediate  DROP PROCEDURE      ObjName                end if     end if    if ObjType    FUNCTION  then     select count    into v counter from User Objects where object type    FUNCTION  and OBJECT NAME   upper ObjName         if v counter  gt  0 then                   execute immediate  DROP FUNCTION      ObjName                end if     end if    if ObjType    TRIGGER  then     select count    into v counter from User Triggers where TRIGGER NAME   upper ObjName         if v counter  gt  0 then                   execute immediate  DROP TRIGGER      ObjName        end if     end if    if ObjType    VIEW  then     select count    into v counter from User Views where VIEW NAME   upper ObjName         if v counter  gt  0 then                   execute immediate  DROP VIEW      ObjName                end if     end if    if ObjType    SEQUENCE  then     select count    into v counter from user sequences where sequence name   upper ObjName         if v counter  gt  0 then                   execute immediate  DROP SEQUENCE      ObjName                end if     end if  end    Hope this helps

User · Answer

One way is to use DBMS ASSERT SQL OBJECT NAME       This function verifies that the input parameter string is a qualified SQL identifier of an existing SQL object    DECLARE     V OBJECT NAME VARCHAR2 30   BEGIN    BEGIN         V OBJECT NAME     DBMS ASSERT SQL OBJECT NAME  tab1            EXECUTE IMMEDIATE  DROP TABLE tab1            EXCEPTION WHEN OTHERS THEN NULL     END  END      DBFiddle Demo

User · Answer

I prefer following economic solution  BEGIN     FOR i IN  SELECT NULL FROM USER OBJECTS WHERE OBJECT TYPE    TABLE  AND OBJECT NAME    TABLE NAME   LOOP             EXECUTE IMMEDIATE  DROP TABLE TABLE NAME       END LOOP  END

User · Answer

BEGIN    EXECUTE IMMEDIATE  DROP TABLE  IMS   MAX     EXCEPTION    WHEN OTHERS THEN       IF SQLCODE    -942 THEN          RAISE            END IF           EXECUTE IMMEDIATE      CREATE TABLE  IMS   MAX            ID  NUMBER NOT NULL ENABLE        NAME  VARCHAR2 20 BYTE         CONSTRAINT  MAX PK  PRIMARY KEY   ID     USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255    STORAGE INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER POOL DEFAULT FLASH CACHE DEFAULT CELL FLASH CACHE DEFAULT    TABLESPACE  SYSAUX   ENABLE      SEGMENT CREATION IMMEDIATE    PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING   STORAGE INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER POOL DEFAULT FLASH CACHE DEFAULT CELL FLASH CACHE DEFAULT    TABLESPACE  SYSAUX        END       Doing this code  checks if the table exists and later it creates the table max  this simply works in single compilation

User · Answer

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

User · Answer

There is no  DROP TABLE IF EXISTS  in oracle  you would have to do the select statement   try this  i m not up on oracle syntax  so if my variables are ify  please forgive me    declare  count int select  count count    from all tables where table name  Table name   if  count gt 0 BEGIN     DROP TABLE tableName  END

User · Answer

declare    c int  begin    select count    into c from user tables where table name   upper  table name       if c   1 then       execute immediate  drop table table name      end if  end    That s for checking whether a table in the current schema exists  For checking whether a given table already exists in a different schema  you d have to use all tables instead of user tables and add the condition all tables owner   upper  schema name

User · Answer

You could always catch the error yourself   begin execute immediate  drop table mytable   exception when others then null  end    It is considered bad practice to overuse this  similar to empty catch   es in other languages   Regards K

User · Answer

With SQL PLUS you can also use the WHENEVER SQLERROR command   WHENEVER SQLERROR CONTINUE NONE DROP TABLE TABLE NAME   WHENEVER SQLERROR EXIT SQL SQLCODE DROP TABLE TABLE NAME    With CONTINUE NONE an error is reported  but the script will continue  With EXIT SQL SQLCODE the script will be terminated in the case of an error   see also  WHENEVER SQLERROR Docs

User · Answer

Another method is to define an exception and then only catch that exception letting all others propagate    Declare    eTableDoesNotExist Exception     PRAGMA EXCEPTION INIT eTableDoesNotExist  -942   Begin    EXECUTE IMMEDIATE   DROP TABLE myschema mytable    Exception    When eTableDoesNotExist Then       DBMS Output Put Line  Table already does not exist     End

User · Answer

A block like this could be useful to you   DECLARE     table exist INT   BEGIN     SELECT Count        INTO   table exist     FROM   dba tables     WHERE  owner    SCHEMA NAME       AND table name    EMPLOYEE TABLE        IF table exist   1 THEN       EXECUTE IMMEDIATE  drop table EMPLOYEE TABLE       END IF  END

User · Answer

just wanted to post a full code that will create a table and drop it if it already exists using Jeffrey s code  kudos to him  not me     BEGIN     BEGIN          EXECUTE IMMEDIATE  DROP TABLE tablename       EXCEPTION          WHEN OTHERS THEN                 IF SQLCODE    -942 THEN                      RAISE                  END IF      END       EXECUTE IMMEDIATE  CREATE TABLE tablename AS SELECT   FROM sourcetable WHERE 1 0    END

[sql] Oracle: If Table Exists

Examples related to sql

Examples related to oracle

Examples related to sql-drop