dropping multiple tables

[Jay]

Hi all

Is there an Oracle PL/SQL script to drop multiple tables ?

Thanks in advance,
Jay

[roy]

Hi,
you can ask your DBAs for one or a SPL that will do it.
or use DS:
you can simply send multiple user defined quries using a DS job.
if you have a dynamic list of tables write it to a seq file, then read it and pass to a DS job as aparameter to drop each table using OCI stage and user defined query.
if you have a rather small and fixed table list you can simply write a ds job doing it hard coded, but usually along time the list may change so I prefer the dynamic method.

I can't remember if OCI supports multiple sql commands in the OCI before/after stage if it dose you can prepare the whole sql (in a transaction) use a dummy select fom dual to NUL and flag the treat errors as fatal in the before/after sql you placed the statements in.

there might be more ways to do it (including use of the ODBC API with DS basic which you'll need to code, search this forum to get it).

IHTH,
IHTH

[kcbland]

How about...

Code: Select all

drop table aaaaaaaaaa;
drop table bbbbbbbbbb;
drop table ccccccccccc;
commit;

Then, put this logic into a .sql file and write a DOS batch file to execute sqlplus with login credentials and give it the path to the above .sql file.

[Jay]

Thanks. I dropped the user altogether and started from scratch. I dont know if this was a good idea but it looks like its working.