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?
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.