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?
Joined: Jan 19, 2006
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
Joined: Jul 20, 2002
I tried. But got an Error "ORA-01722: invalid number"
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.