File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes Drop tables recursively Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Drop tables recursively" Watch "Drop tables recursively" New topic
Author

Drop tables recursively

Jane Somerfield
Ranch Hand

Joined: Jul 20, 2002
Posts: 193
I need to drop a group of tables, whose names are started with �MY_�. Because the number of the table is very large (around 200), I want to do it recursively. Here is the SQL with subquery, but it is not working.

DROP TABLE (select TABLE_NAME from ALL_TABLES where TABLE_NAME like 'MY_%') CASCADE CONSTRAINTS PURGE;

Any suggestions? Any Oracle (PL/SQL) functions can do it?
Edisandro Bessa
Ranch Hand

Joined: Jan 19, 2006
Posts: 584
Hi Jane,

I think you could try something like this :

SELECT 'DROP TABLE ' + TABLE_NAME + ' CASCADE CONSTRAINTS PURGE; '
FROM ALL_TABLES WHERE TABLE_NAME LIKE 'MY_%'

The above SQL statement would create 200 drop commands like this :

DROP TABLE MY_TABLE1 CASCADE CONSTRAINTS PURGE;
DROP TABLE MY_TABLE2 CASCADE CONSTRAINTS PURGE;
DROP TABLE MY_TABLE3 CASCADE CONSTRAINTS PURGE;
DROP TABLE MY_TABLE4 CASCADE CONSTRAINTS PURGE;

... And so on ...

I'm not sure about oracle PL-SQL syntax, so you can adapt the sql statement above according to your needs.

If the first command works on Oracle, all of them will work.
Everything you have to do is submit all these commands in only one shot.
[ May 24, 2007: Message edited by: Edisandro Bessa ]

"If someone asks you to do something you don't know how to, don't tell I don't know, tell I can learn instead." - Myself
Jane Somerfield
Ranch Hand

Joined: Jul 20, 2002
Posts: 193
I tried. But got an Error "ORA-01722: invalid number"
Scott Selikoff
author
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3716
    
    5

I doubt there is a way to do it mostly because creating/dropping is not something that should be done frequently (such that optimization is really necessary).

If you're in a situation where tables need to be dropped often (especially 200), I'd wonder if you should rethink your schema. BTW, even if you can't do this in PLSQL, you can always do what your describing pretty easily in JDBC just using a while loop on the first SELECT statement, but again, I'd wonder more about why this is necessary.


My Blog: Down Home Country Coding with Scott Selikoff
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Drop tables recursively