drop table If it exists in Oracle (IF EXIST)

Question

I am using Oracle 12c and I am not interesting to have an error while droping my table 'CONTINENT' in case it doesn't exist.

I did this

set echo on
set serveroutput on
alter session set current_schema=WORK_ODI;
set verify off
set pause off

begin
  execute immediate 'drop table continent';
  exception when others then null;
end;

This script is work with me well. and I use this script too :

declare
   c int;
begin
   select count(*) into c from user_tables where table_name = upper('continent');
   if c = 1 then
      execute immediate 'drop table continent';
   end if;
end;

the both scripts work well but my boss wants something like IF EXIT. Anybody can help me please. how to use IF EXIT in this case ?

Answer 1

You can do two things

• define the exception you want to ignore (here ORA-00942)

• add an undocumented (and not implemented) hint /*+ IF EXISTS */ that will pleased your management.

.

declare
  table_does_not_exist exception;
  PRAGMA EXCEPTION_INIT(table_does_not_exist, -942);
begin
  execute immediate 'drop table continent /*+ IF EXISTS */';
  exception when table_does_not_exist then 
        DBMS_OUTPUT.PUT_LINE('Ignoring table or view does not exist')
   ;
end;
/

Additional note: the usage of

 exception when others then null;

may be dangerous, as for example you ignore also errors such as tablespace offline, when the table is NOT DROPPED.

Answer 2

Sorry, there is no if exists in the Oracle's drop table syntax.

Answer 3

set echo on
set serveroutput on
alter session set current_schema=WORK_ODI;
set verify off
set pause off

WHENEVER OSERROR EXIT FAILURE ROLLBACK
drop table continent;
WHENEVER OSERROR CONTINUE

Answer 4

I had a similar problem - i needed a way how to repeat DDL scripts without modifying them. Imaging the following script:

create table tab1(...);

create table tab2(...);

create table tab3{...}; /* <--- this one fails*/

create table tab4(...);

so now we have the following situation: tables "tab1" and "tab2" have been successfully created, "tab3" and "tab4" are missing. So after fixing statement for the "tab3" table we would have to comment out create statements for "tab1" and "tab2" - it might be very annoying when working with big SQL scripts containing many DDLs and many bugs.

So i came up with the following procedure which allows to re-run DDL statements:

create or replace procedure re_run_ddl (p_sql in varchar2)
AUTHID CURRENT_USER
as
  l_line        varchar2(500)   default rpad('-',20,'-');
  l_cr          varchar2(2)     default chr(10);
  l_footer      varchar2(500)   default l_cr||rpad('*',20,'*');
  l_ignore_txt  varchar2(200)   default 'IGNORING --> ';
  ORA_00955 EXCEPTION;
  ORA_01430 EXCEPTION;
  ORA_02260 EXCEPTION;
  ORA_01408 EXCEPTION;
  ORA_00942 EXCEPTION;
  ORA_02275 EXCEPTION;
  ORA_01418 EXCEPTION;
  ORA_02443 EXCEPTION;
  ORA_01442 EXCEPTION;
  ORA_01434 EXCEPTION;
  ORA_01543 EXCEPTION;
  ORA_00904 EXCEPTION;
  ORA_02261 EXCEPTION;
  ORA_04043 EXCEPTION;
  ORA_02289 EXCEPTION;
  PRAGMA EXCEPTION_INIT(ORA_00955, -00955); --ORA-00955: name is already used by an existing object
  PRAGMA EXCEPTION_INIT(ORA_01430, -01430); --ORA-01430: column being added already exists in table
  PRAGMA EXCEPTION_INIT(ORA_02260, -02260); --ORA-02260: table can have only one primary key
  PRAGMA EXCEPTION_INIT(ORA_01408, -01408); --ORA-01408: such column list already indexed
  PRAGMA EXCEPTION_INIT(ORA_00942, -00942); --ORA-00942: table or view does not exist
  PRAGMA EXCEPTION_INIT(ORA_02275, -02275); --ORA-02275: such a referential constraint already exists in the table
  PRAGMA EXCEPTION_INIT(ORA_01418, -01418); --ORA-01418: specified index does not exist
  PRAGMA EXCEPTION_INIT(ORA_02443, -02443); --ORA-02443: Cannot drop constraint  - nonexistent constraint
  PRAGMA EXCEPTION_INIT(ORA_01442, -01442); --ORA-01442: column to be modified to NOT NULL is already NOT NULL
  PRAGMA EXCEPTION_INIT(ORA_01434, -01434); --ORA-01434: private synonym to be dropped does not exist
  PRAGMA EXCEPTION_INIT(ORA_01543, -01543); --ORA-01543: tablespace '<TBS_NAME>' already exists
  PRAGMA EXCEPTION_INIT(ORA_00904, -00904); --ORA-00904: "%s: invalid identifier"
  PRAGMA EXCEPTION_INIT(ORA_02261, -02261); --ORA-02261: "such unique or primary key already exists in the table"
  PRAGMA EXCEPTION_INIT(ORA_04043, -04043); --ORA-04043: object %s does not exist
  PRAGMA EXCEPTION_INIT(ORA_02289, -02289); --ORA-02289: sequence does not exist
  procedure p(
         p_str      in  varchar2
        ,p_maxlength    in  int     default 120
  )
  is
     i      int := 1;
  begin
    dbms_output.enable( NULL );

    while ( (length(substr(p_str,i,p_maxlength))) = p_maxlength ) loop
        dbms_output.put_line(substr(p_str,i,p_maxlength));
        i := i + p_maxlength;
    end loop;

    dbms_output.put_line(substr(p_str,i,p_maxlength));
  end p;
begin

  p( 'EXEC:'||l_cr||l_line||l_cr||p_sql||l_cr||l_line );

  execute immediate p_sql;

  p( 'done.' );

exception
  when  ORA_00955 or ORA_01430 or ORA_02260 or ORA_01408 or ORA_00942
        or ORA_02275 or ORA_01418 or ORA_02443 or ORA_01442 or ORA_01434
        or ORA_01543 or ORA_00904 or ORA_02261 or ORA_04043 or ORA_02289
    then p( l_ignore_txt || SQLERRM || l_footer );
  when OTHERS then
    p( SQLERRM );
    p( DBMS_UTILITY.FORMAT_ERROR_BACKTRACE );
    p( l_footer );
    RAISE;
end;
/
show err

Usage example:

set serveroutput on
begin
re_run_ddl('
create table test
(
  id    number,
  s     varchar2(30) 
)
');
end;
/
exec re_run_ddl('drop table test');
exec re_run_ddl('drop table test');
exec re_run_ddl('drop table test');

Output:

EXEC:
--------------------

create table test
(
  id    number,
  s     varchar2(30)
)

--------------------
done.

PL/SQL procedure successfully completed.

EXEC:
--------------------
drop table test
--------------------
done.

PL/SQL procedure successfully completed.

stx11de2> EXEC:
--------------------
drop table test
--------------------
IGNORING --> ORA-00942: table or view does not exist
********************

PL/SQL procedure successfully completed.

stx11de2> EXEC:
--------------------
drop table test
--------------------
IGNORING --> ORA-00942: table or view does not exist
********************

PL/SQL procedure successfully completed.