I'll just repeat what I learned since no one tells how the deletion works. I came to learn about how to delete one function. I'm trying to use a procedure no parameters to drop all of the user-created database objects located within the schema from where the procedure is launched, but I'm really not sure on how to go about this. But Vijayan Srinivasan's version is not correct, because dependent objects of type 'TYPE' sometime generates errors during drop them: Are you not shooting yourself?
Thanks Martin Brambley and Vijayan Srinivasan! Dropping the user isn't an option right now. Unless the user has hard to reapply permissions, its probably easier to just drop the user and recreate them.
Up to version 10g at least you couldn't even drop the containing schema without putting the database in a special mode when this situation existed. Here is the rest: Although you can drop the Oracle-generated tables for queue support with a regular DROP TABLE, you will find yourself in the catch-22 position of then not being able to drop the related queues nor add them back. Thanks Martin Brambley, I feel we can simplify your answer in the following way.
Related 1006. But Vijayan Srinivasan's version is not correct, because dependent objects of type 'TYPE' sometime generates errors during drop them:. Remember that you'll probably be generating the cursor list in an order that doesn't consider dependencies that will block the drop.
You have a cursor AND a select statement.
Lastly, this is obviously a very dangerous method so you may want to consider putting it in a script instead of a stored procedure so it isn't left in the database for anyone to run. What you've got is a good start.
Sign up using Email and Password. NMan NMan 233 2 5 10.
Hot Network Questions. Also on the subject of dependencies, it is probably best to drop tables first add a DECODE in your cursor that assigns a lower numeric value to this object type and order the cursor select by this value.