• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Drop tables recursively

 
Ranch Hand
Posts: 193
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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?
 
Ranch Hand
Posts: 584
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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 ]
 
Jane Somerfield
Ranch Hand
Posts: 193
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I tried. But got an Error "ORA-01722: invalid number"
 
author
Posts: 4335
39
jQuery Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
reply
    Bookmark Topic Watch Topic
  • New Topic