MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Drop Object If Exists

with 6 comments

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'));

Written by maclochlainn

March 7th, 2011 at 12:31 am

Posted in Oracle,Oracle XE,sql

«
»