Re: SQL*Plus code in ksh script, in-line vs. separate file

You know this would really be easier, and maybe more secure, to implement as a procedure or anonymous block scheduled through DBMS_JOB ...

Begin
For x in (select table_name from all_tables where owner='MVXJDTA' and table_name like 'T\_%'escape '\') Loop

   Execute Immediate 'Drop table MVXJDTA.'||x.table_name; End Loop;
End;
/

You could log to a table or even to a file using UTL_FILE

You might also query all_objects instead, and check the last_ddl_time or created columns in order to avoid dropping table that were created just a few seconds before, and which might still be in use. Received on Fri Feb 11 2005 - 17:11:01 CST