Drop if exists…

When creating an update script for the database the DBA always nag me about objects to be dropped that don’t exist. My view is that the end result is the same, you are dropping an objects to remove it, so in the end it’s gone. If it’s not there in the first place, then it’s not there after the statement ran (gone as well).

drop table patrick purge
ORA-00942: table or view does not exist

The DBA doesn’t want to see any ORA messages, so if you want your script to drop the object you first have to check for the existence of the object and if it does, then drop it. If it doesn’t then there is nothing to do. (Oracle) MySQL has the possibility to drop if exists. That’s why I created a utility that can be used to suppress any error message. Not, if there are dependencies to an object to be dropped, you will still get errors, but these are errors you want to see and which means ‘back to the drawing board’ for your script.

create or replace procedure drop_if_exists(object_name_in in varchar2,
                                           purge_in       in boolean default true)
  authid current_user as
  -- Procedure checks for the existance of an object and then drops it.
  -- %param object_name_in The name of the object to be dropped
  -- %param purge_in If the object to be dropped is a table, it can be purged from the Recycle bin
  --                {*} True (Default)
  --                {*} False
  -- %author Patrick Barel
  -- %version 1.0

  l_type user_objects.object_type%type;
  l_sql  varchar2(32767);
    select uo.OBJECT_TYPE
      into l_type
      from user_objects uo
     where uo.OBJECT_NAME = upper(object_name_in);
    when others then
      dbms_output.put_line('Nothing to do');
  if l_type is not null then
    l_sql := 'DROP ' || l_type || ' ' || object_name_in;
    if (l_type = 'TABLE') and purge_in then
      l_sql := l_sql || ' PURGE';
    end if;
    execute immediate l_sql;
    dbms_output.put_line(object_name_in || ' dropped');
  end if;

A way to call this procedure is like this:

-- Drop table
--drop table AMIS_LOG purge;
exec drop_if_exists(object_name_in => 'AMIS_LOG')
-- Create table
create table AMIS_LOG
  log_id             number,

If you have any suggestions for this script, let me know. This is a very simple version which doesn’t do all the exception handling possible.

5 thoughts on “Drop if exists…

  1. I’d probably switch the ‘WHEN OTHERS’ to a ‘WHEN NO_DATA_FOUND’.
    Technically it wouldn’t work for tables explicitly created with lowercase names by quoting. But then if people use stuff like that, they deserve what they get.

  2. I don’t remember where I read it but how about following approach?
    If number of tables to be dropped (and recreated) are in small number (less than 100 and the only reason for this is to keep the script readable), one can do something like this:
    for x in (select table_name from all_tables where table_name in()
    execute immediate ‘drop table ‘ || x.table_name ;
    end loop;
    Depending upon how intelligent you want to make it, you might want to check for failure of DROP TABLE commands.

  3. In the past I’ve included something along these lines within the script (template)

    execute immediate ‘DROP TABLE dud_table’;
    when others then
    if sqlcode != -0942 then
    end if;

  4. I agree with you, a DBA shouldn’t complain about a few ORA mesages. But gues what happens if you try this script on a table which has a synonym with the same name, or a package. Too_many_rows exception? Handled by the script, so the DBA won’t complain, but nothing is dropped.

Leave a Reply

Your email address will not be published.