Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2004
    Posts
    5

    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...*/'

    drop.sql:
    ================================
    set heading off
    set newpage none
    set serveroutput on size 100000
    DECLARE
    CUR INTEGER;
    OBJNAME VARCHAR2(128);
    CURSOR C1 IS SELECT OBJECT_NAME FROM USER_OBJECTS
    WHERE OBJECT_TYPE='TABLE';
    BEGIN
    OPEN C1;
    LOOP
    BEGIN
    FETCH C1 INTO OBJNAME;
    EXIT WHEN C1%NOTFOUND;
    CUR:=DBMS_SQL.OPEN_CURSOR;
    DBMS_OUTPUT.PUT_LINE('Dropping' ||
    ' TABLE ' || OBJNAME);
    DBMS_SQL.PARSE(CUR,
    'DROP TABLE ' || OBJNAME || ' CASCADE CONSTRAINTS'
    , DBMS_SQL.NATIVE);
    DBMS_SQL.CLOSE_CURSOR(CUR);
    EXCEPTION WHEN OTHERS THEN
    dbms_output.put_line('Exception encountered for
    ' || OBJNAME);
    END;
    END LOOP;
    CLOSE C1;
    COMMIT;
    EXCEPTION WHEN OTHERS THEN NULL;
    END;
    /
    quit

  2. #2
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    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.
    NOTE: Please disregard the label "Senior Member".

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •