CODEWITHSUNDEEP
oracle11gdatabase-schemaddl
santosh
santosh

Reputation: 425

How can drop table if table exists in oracle?

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.

Upvotes: 1

Views: 22421

Answers (3)

Amit Kumar
Amit Kumar

Reputation: 1

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;

Upvotes: 0

Sergiu Velescu
Sergiu Velescu

Reputation: 514

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;

Upvotes: 2

hinotf
hinotf

Reputation: 1138

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;

Upvotes: 6

Related Questions