/*
 * Script to DROP *ALL* OBJECTS IN THE CURRENT SCHEMA.  Use this with caution!!!
 * 
 * by Trey Kinkead
 */
DECLARE

    l_cnt       NUMBER;
    i           NUMBER;

    CURSOR c_objects
    IS
        SELECT
            object_name,
            object_type
        FROM
            user_objects;

    l_foo       BOOLEAN;

    r_object    c_objects%ROWTYPE;
    
BEGIN

    SELECT COUNT(*) INTO l_cnt FROM user_objects;

    i := l_cnt;
    WHILE i > 0 LOOP

        OPEN c_objects;
        LOOP
            FETCH c_objects INTO r_object;
            EXIT WHEN c_objects%NOTFOUND;

            BEGIN
                IF (r_object.object_type = 'TABLE') THEN
                    EXECUTE IMMEDIATE 'DROP TABLE '||r_object.object_name||' CASCADE CONSTRAINTS';
                ELSE
                    EXECUTE IMMEDIATE 'DROP '||r_object.object_type||' '||r_object.object_name;
                END IF;
            EXCEPTION
                WHEN OTHERS THEN
                    l_foo := true;
            END;
        END LOOP;

        CLOSE c_objects;

        i:=i-1;
        
        SELECT COUNT(*) INTO l_cnt FROM user_objects;
        IF ( l_cnt < i ) THEN
            i := l_cnt;
        END IF;
        
    END LOOP;
END;
/