Kaley's (mostly) Oracle Database Blog
Hi! I'm Kaley Crum, Oracle DBA.
Welcome to my (mostly) Oracle database blog.

Oracle Drop If Exists Procedure

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.

Leave a Reply

Your email address will not be published. Required fields are marked *

ORA-01031 Insufficient Privileges Reference

See All Tables Foreign-Keyed to a Parent Table in Oracle