Drop Object If Exists
Writing an anonymous block to conditionally drop tables and sequences got very old. I figured it was time to simply put a procedure that would simplify it. Avoiding repetition was important too, so it supports an invoker rights model. This mimics the IF EXISTS
syntax available in MySQL.
You need to create this as the SYSTEM
user and then grant execute permission on the procedure to PUBLIC
. Then, you need to create a couple public synonyms.
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 | -- Create a procedure to simplify dropping objects. CREATE OR REPLACE PROCEDURE drop_ifexists ( pv_type VARCHAR2 , pv_table VARCHAR2 ) AUTHID CURRENT_USER IS -- String for DDL command. sql_text VARCHAR2(2000); -- Declare a parameterized cursor. CURSOR find_object ( cv_type VARCHAR2 , cv_table VARCHAR2 ) IS SELECT uo.object_name , uo.object_type FROM user_objects uo WHERE uo.object_name = UPPER(cv_table) AND uo.object_type = UPPER(cv_type); BEGIN -- Open the cursor with the input variables. FOR i IN find_object(pv_type, pv_table) LOOP -- Check for a table object and append cascade constraints. IF i.object_type = 'TABLE' THEN sql_text := 'DROP '||i.object_type||' '||i.object_name||' CASCADE CONSTRAINTS'; ELSE sql_text := 'DROP '||i.object_type||' '||i.object_name; END IF; -- Run dynamic command. EXECUTE IMMEDIATE sql_text; END LOOP; END drop_ifexists; / |
After creating the procedure in the SYSTEM
user schema, you should run these Data Control Language (DCL) commands:
GRANT EXECUTE ON drop_ifexists TO PUBLIC; CREATE PUBLIC SYNONYM drop_ifexists FOR system.drop_ifexists; |
Assuming you have a table named MESSAGE
, you can call the drop_ifexists
procedure to conditionally delete it as follows below. The same procedure works with any object in a user’s schema.
EXECUTE drop_ifexists('table','message'); |
The only problem comes with types because they can have dependents. When they have dependents you can’t drop them until you’ve removed all the dependents. There are two types of dependents types and collection types.
Since discovering those may be difficult for new users, here’s another invoker rights function to discover user defined types. You should note that you’ll need to create the types before you try to compile the code (create the stored program in the Oracle database).
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 | -- Create a SQL structure. CREATE OR REPLACE TYPE dependent_type_obj IS OBJECT ( dependent_type VARCHAR2(10) , type_name VARCHAR2(30)); / -- Create a SQL collection of a user-defined data structure. CREATE OR REPLACE TYPE dependent_type_tab IS TABLE OF dependent_type_obj; / -- Create a procedure to discover type dependents. CREATE OR REPLACE FUNCTION type_dependents ( pv_type_name VARCHAR2 ) RETURN dependent_type_tab AUTHID CURRENT_USER IS -- Declare a counter for the collection variable. c NUMBER := 1; -- Declare a return type variable. list DEPENDENT_TYPE_TAB := dependent_type_tab(); -- Declare a parameterized cursor for dependent types. CURSOR find_dependent_types ( cv_type VARCHAR2 ) IS SELECT 'STANDALONE' AS dependent_type , at.type_name FROM all_types at INNER JOIN all_type_attrs ata ON at.type_name = ata.type_name WHERE at.owner = USER AND ata.attr_type_name = UPPER(cv_type) UNION ALL SELECT 'COLLECTION' AS dependent_type , act.type_name FROM all_types at INNER JOIN all_coll_types act ON at.type_name = act.elem_type_name WHERE at.owner = USER AND act.elem_type_name = UPPER(cv_type); BEGIN -- Loop through all returns and add them to the return collection. FOR i IN find_dependent_types(pv_type_name) LOOP list.EXTEND; list(c) := dependent_type_obj(i.dependent_type, i.type_name); c := c + 1; END LOOP; -- Return the list. RETURN list; END type_dependents; / |
You should also make the following grants and synonyms:
GRANT EXECUTE ON system.type_dependents TO PUBLIC; CREATE PUBLIC SYNONYM type_dependents FOR system.type_dependents; GRANT ALL ON system.dependent_type_obj TO PUBLIC; GRANT ALL ON system.dependent_type_tab TO PUBLIC; |
Then, you can call the type_dependents
function to find any dependent user-defined data types. If so, you must drop them from the bottom or most specialized straight up the type tree to the root node data type.
SELECT * FROM TABLE(type_dependents('&type_name')); |