Unanswered: drop all tables procedures script with progress dots
I'm working on a script to drop tables, procedures, etc. but I wanted the
output from the SQL block so that I could print dots (......) to show progress or do feedback like 'dropped table FOO; dropped table BAR;etc.', but my drop.sql script didn't echo anything as it operated.
I put a dbms_output.put_line command in which kinda works. It took me a while to get the 'set serveroutput on 100000' tip. Now I get my output, but only after the script is done. Is there a way to have it not buffer this output so it prints it out as it runs?
I need this to work as a .sql file from the command line:
sqlplus user/pass@sid @drop.sql | awk '/*my reporting script...*/'
set heading off
set newpage none
set serveroutput on size 100000
CURSOR C1 IS SELECT OBJECT_NAME FROM USER_OBJECTS
FETCH C1 INTO OBJNAME;
EXIT WHEN C1%NOTFOUND;
' TABLE ' || OBJNAME);
'DROP TABLE ' || OBJNAME || ' CASCADE CONSTRAINTS'
EXCEPTION WHEN OTHERS THEN
dbms_output.put_line('Exception encountered for
' || OBJNAME);
EXCEPTION WHEN OTHERS THEN NULL;
down on the cube farm, left then right then another left
Check out DBMS_ALERT. I read a post earlier today "Exception Handling" (http://www.dbforums.com/showthread.php?threadid=926485) where one of the responders talked about DBMS_ALERT. It might be what you want. As an aside I hate the "show stuff at the end" mentallity of the PLSQL stuff also.