One of the things I’ve always wanted in Oracle was drop if exists functionality. Inevitably, I’ll be creating a script that creates a bunch of tables and, for one reason or another, I’ll need to re-run the script because a data type in the table has changed or the length of a field needs to be tweaked, so I start dropping tables and I’ll miss one, or I’ll miss and index, and as a result the script dies halfway through with a ORA-00955 name is already used by an existing object error.
The solution, I’ve determined, was to roll my own drop_if_exists() procedure.
You call it like this:
exec drop_if_exists(‘[schema_name.]object_name'[, ‘object type’]);
Warning/Room for Improvement
Admittedly, I’ve not thoroughly tested this across all database object types. I’ve mainly only used it against tables, so just a note that this code doesn’t have any guarantees, expressed or implied, so use at your own risk. Also, I know for a fact that this could be made simpler.
One of the things worth noting about this is it queries the ALL_OBJECTS data dictionary table to try to figure out what type of object you’re trying to drop, and of course the ALL_OBJECTS table only lists the objects that you have access to, so there stands the possibility that someone using this function might be trying to drop, for example, a database link, which doesn’t show up in ALL_OBJECTS because they don’t have access to it, but a table with the same name of the table does show up in ALL_OBJECTS, so the procedure drops the table instead of the database link. To prevent against this, you can optionally specifying the type of object that you wish to drop.
So, for example, if you have a table called KALEYS_ONE_OF_A_KIND_TABLE and you’re logged in under the user that owns it and you know for a fact that no other database object (including tables, indexes, database links, views, procedures, functions, etc, etc, etc) are owned by you with this same name, then the procedures allows you the flexibility to call it like this:
If you don’t own that table, you can specify who does own it. So, say you knew that the KALEYS_ONE_OF_A_KIND table resided in the KALEY schema. If you know for a fact that no other database objects (including tables, indexes, database links, views, procedures, functions, etc, etc, etc) in the KALEY schema have this same name, you can call the procedure like this:
If you know (or are afraid) that another database object might share the same name as what it is, you’re trying to drop, you can specify the type of object that you’re looking to potentially drop. So you can run this:
exec drop_if_exists('kaley.kaleys_one_of_a_kind_table', 'table');
…and you won’t have to worry about the scenario I described above where you might accidentally drop an index or a database link or a function or whatever other object with the same name that doesn’t show up in your view of ALL_OBJECTS.
Now, if there are two objects that appear with the same name owned by the same people in ALL_OBJECTS and the procedure can’t figure out which object it is you’re trying to drop, the function will die without dropping either and give you a warning message about finding multiple objects. So really the only danger is when one of the objects that you might be trying to drop doesn’t appear in your view of ALL_OBJECTS.
create or replace procedure drop_if_exists(v_object_name in varchar2, v_object_type in varchar2 default null)
v_location_of_dot := instr(v_object_name, '.');
if v_location_of_dot = 0 then
--Object was not prefixed with schema,
--meaning object must be in current schema
v_schema_name := user;
v_unqualified_object_name := v_object_name;
--If object was prefixed with schema
v_schema_name := substr(v_object_name, 1, v_location_of_dot - 1);
v_unqualified_object_name := substr(v_object_name, v_location_of_dot + 1);
if v_schema_name is null
instr(v_unqualified_object_name, '.') > 0
raise_application_error(-20999, 'Invalid Schema supplied');
-- If identifiers are wrapped in quotes, get rid of them.
-- If not wrapped in quotes, capitalize them.
if regexp_like(v_unqualified_object_name, '^".+"$') then
v_unqualified_object_name := regexp_replace(v_unqualified_object_name, '^"(.+)"$', '\1');
v_unqualified_object_name := upper(v_unqualified_object_name);
if regexp_like(v_schema_name, '^".+"$') then
v_schema_name := regexp_replace(v_schema_name, '^"(.+)"$', '\1');
v_schema_name := upper(v_schema_name);
select dbms_assert.schema_name(v_schema_name) into v_schema_name
v_sql := q'<
where object_name = :object_name
and owner = :owner
and (object_type = :object_type or :object_type is null)
and object_type in ('INDEX', 'TABLE', 'SYNONYM', 'VIEW', 'TRIGGER', 'TYPE', 'TYPE BODY', 'PACKAGE', 'PACKAGE BODY', 'PROCEDURE', 'FUNCTION', 'DATABASE LINK', 'DIRECTORY', 'MATERIALIZED VIEW', 'SEQUENCE')>';
v_upper_object_type := upper(v_object_type);
v_num_result_set_rows := 0;
open v_cursor for v_sql using v_unqualified_object_name, v_schema_name, v_upper_object_type, v_upper_object_type;
fetch v_cursor into v_data_dict_obj_type;
exit when v_cursor%NOTFOUND;
v_num_result_set_rows := v_num_result_set_rows + 1;
if v_num_result_set_rows > 1 then
raise_application_error(-20999, 'Multiple objects found for ' || v_schema_name || '.' || v_unqualified_object_name || '.' || chr(10) ||
'Please specify which object to drop by running, for example, drop_if_exists(' || v_object_name || q'<, '>' || v_data_dict_obj_type || q'<')>');
-- If we didn't find the object, nothing to drop.
if v_num_result_set_rows = 0 then
dbms_output.put_line('Object not found');
v_sql := 'drop ' || v_data_dict_obj_type || ' ';
if v_schema_name is not null then
v_sql := v_sql || v_schema_name || '.';
v_sql := v_sql || v_unqualified_object_name;
execute immediate v_sql;
dbms_output.put_line(initcap(v_data_dict_obj_type) || ' dropped.');
-- when others then raise;