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:
exec drop_if_exists('kaleys_one_of_a_kind_table');
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:
exec drop_if_exists('kaley.kaleys_one_of_a_kind_table');
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 |
create or replace procedure drop_if_exists(v_object_name in varchar2, v_object_type in varchar2 default null) authid current_user is v_location_of_dot number; v_schema_name varchar2(64); v_unqualified_object_name varchar2(64); v_sql varchar2(512); v_cursor SYS_REFCURSOR; v_data_dict_obj_type varchar2(64); v_num_result_set_rows number; v_upper_object_type varchar2(64); begin 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; else --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 or instr(v_unqualified_object_name, '.') > 0 then raise_application_error(-20999, 'Invalid Schema supplied'); end if; end if; -- 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'); else v_unqualified_object_name := upper(v_unqualified_object_name); end if; if regexp_like(v_schema_name, '^".+"$') then v_schema_name := regexp_replace(v_schema_name, '^"(.+)"$', '\1'); else v_schema_name := upper(v_schema_name); end if; select dbms_assert.schema_name(v_schema_name) into v_schema_name from dual; v_sql := q'< select object_type from all_objects 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; loop 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'<')>'); end if; end loop; -- 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'); return; else v_sql := 'drop ' || v_data_dict_obj_type || ' '; if v_schema_name is not null then v_sql := v_sql || v_schema_name || '.'; end if; v_sql := v_sql || v_unqualified_object_name; execute immediate v_sql; dbms_output.put_line(initcap(v_data_dict_obj_type) || ' dropped.'); end if; -- exception -- when others then raise; end drop_if_exists; / |