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); begin begin select uo.OBJECT_TYPE into l_type from user_objects uo where uo.OBJECT_NAME = upper(object_name_in); exception when others then dbms_output.put_line('Nothing to do'); end; 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; end;
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.
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.
Thank you for nice script.
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()
loop
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.
In the past I’ve included something along these lines within the script (template)
begin
execute immediate ‘DROP TABLE dud_table’;
exception
when others then
if sqlcode != -0942 then
raise;
end if;
end;
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.