How can drop table if table exists in oracle?

Question

I am trying to create database by my java application using my generated schema file. In schema I have included drop query also. But I want to do some improvements for DROP QUERY. So I want to check the existence of db objects before running drop query and drop only when if it exists. I googled for it and found some oracle link, Some link suggest following syntax and some mentioned that ORACLE does not support such syntax.

 SYNTAX A: 
IF EXISTS DROP TABLE TABLE_NAME

SYNTAX B:
DROP [TEMPORARY] TABLE [IF EXISTS]
        tbl_name [, tbl_name] ...
        [RESTRICT | CASCADE]

I also tried following queries:-

IF EXISTS (SELECT * FROM dba_objects WHERE OBJECT_NAME = 'BBB'  )
DROP TABLE [BBB]

but it was giving error:-

Error starting at line 2 in command:
DROP TABLE [BBB]
Go
Error report:
SQL Error: ORA-00903: invalid table name
00903. 00000 -  "invalid table name"
*Cause:    
*Action:

Error starting at line 1 in command:
IF EXISTS (SELECT * FROM dba_objects WHERE OBJECT_NAME = 'BBB'  ) DROP TABLE [BBB]
Error report:
Unknown Command

I refered following links:- https://community.oracle.com/thread/2421779?tstart=0

Please suggest me if there any other queries to drop table with condition if table exists.

Possible duplicate of [Oracle: If Table Exists](http://stackoverflow.com/questions/1799128/oracle-if-table-exists) — OneCricketeer, Feb 16 '16 at 05:22

score 5 · Answer 1 · answered Feb 16 '16 at 07:35

Drop table with no check. If any error exists you'll never know when something went wrong.

BEGIN
  EXECUTE IMMEDIATE 'DROP TABLE my_table';
EXCEPTION
  WHEN OTHERS THEN
    NULL;
END;

Or you can search in Oracle dictionary.

DECLARE
  l_cnt  NUMBER;
BEGIN  
  SELECT count(*)
    INTO l_cnt
    FROM user_tables
   WHERE table_name = 'MY_TABLE';
  IF l_cnt = 1 THEN
      EXECUTE IMMEDIATE 'DROP TABLE my_table';
  END IF;
END;

score 1 · Accepted Answer · answered Feb 16 '16 at 19:12

1

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;

answered Feb 16 '16 at 19:12

Sergiu Velescu

500
3
9

Above mention block will delete only tables and i want delete all objects of database. – santosh Feb 18 '16 at 07:36
If you want to drop all objects then you should select from DBA_OBJECTS. Oracle DB can store a lot of object types (tables, partitions, types, packages, procedure, functions, synonyms, materialize views, DBLinks, Directories and many others) each object type has it's own "drop" statement (DROP TABLE, DROP SYSNSNYM, DROP DIRECTORY and so on). Maybe the best solution for this task is to drop the user and re-create it (this will drop all user's objects). – Sergiu Velescu Feb 18 '16 at 13:50
Oracle does not have such statement "...IF EXISTS" as MySQL has – Sergiu Velescu Feb 18 '16 at 13:52

score 0 · Answer 3 · answered Jan 15 '18 at 11:32

Try this : It will drop table 'table_name' if it is present .

declare
 a varchar2(700) ;
begin
   execute immediate '  SELECT CASE WHEN  tab = 1
                                    THEN  ''DROP TABLE TABLE_NAME''
                                    ELSE  ''select 1 from dual''
                                END
                         FROM (  SELECT sum(case when table_name = ''TABLE_NAME'' then 1 else 0 end ) as tab  FROM user_tables)' into a;
   EXECUTE IMMEDIATE a;
end;

How can drop table if table exists in oracle?

3 Answers3

Try this : It will drop table 'table_name' if it is present .