-- |----------------------------------------------------------------------------|
-- | NOTE : Make sure we connect to the specific schema of which we are |
-- | going to drop the objects |
-- | environment before attempting to run it in production. |
-- +----------------------------------------------------------------------------+
BEGIN
FOR V_DATA IN ( SELECT V.VIEW_NAME
, V.VIEW_TYPE
FROM USER_VIEWS V ) LOOP
EXECUTE IMMEDIATE ('DROP VIEW '||V_DATA.VIEW_NAME);
END LOOP;
FOR I IN ( SELECT OBJECT_NAME ,
OBJECT_TYPE
FROM USER_OBJECTS
WHERE OBJECT_TYPE = 'JAVA RESOURCE'
OR OBJECT_TYPE= 'JAVA CLASS' ) LOOP
EXECUTE IMMEDIATE ('DROP '|| I.OBJECT_TYPE || ' "' || I.OBJECT_NAME || '"');
END LOOP;
FOR I IN ( SELECT OBJECT_NAME
, OBJECT_TYPE
FROM USER_OBJECTS
WHERE OBJECT_TYPE = 'PACKAGE BODY') LOOP
EXECUTE IMMEDIATE ('DROP '|| I.OBJECT_TYPE || ' "' || I.OBJECT_NAME || '"');
END LOOP;
FOR I IN ( SELECT OBJECT_NAME
, OBJECT_TYPE
FROM USER_OBJECTS
WHERE OBJECT_TYPE = 'PACKAGE') LOOP
EXECUTE IMMEDIATE ('DROP '|| I.OBJECT_TYPE || ' "' || I.OBJECT_NAME || '"');
END LOOP;
FOR I IN ( SELECT OBJECT_NAME
, OBJECT_TYPE
FROM USER_OBJECTS
WHERE OBJECT_TYPE = 'PROCEDURE') LOOP
EXECUTE IMMEDIATE ('DROP '|| I.OBJECT_TYPE || ' "' || I.OBJECT_NAME || '"');
END LOOP;
FOR I IN ( SELECT OBJECT_NAME
, OBJECT_TYPE
FROM USER_OBJECTS
WHERE OBJECT_TYPE = 'FUNCTION') LOOP
EXECUTE IMMEDIATE ('DROP '|| I.OBJECT_TYPE || ' "' || I.OBJECT_NAME || '"');
END LOOP;
FOR I IN ( SELECT OBJECT_NAME
, OBJECT_TYPE
FROM USER_OBJECTS
WHERE OBJECT_TYPE = 'SYNONYM') LOOP
EXECUTE IMMEDIATE ('DROP '|| I.OBJECT_TYPE || ' "' || I.OBJECT_NAME || '"');
END LOOP;
FOR I IN ( SELECT OBJECT_NAME
, OBJECT_TYPE
FROM USER_OBJECTS
WHERE OBJECT_TYPE = 'MATERIALIZED VIEW') LOOP
EXECUTE IMMEDIATE ('DROP '|| I.OBJECT_TYPE || ' "' || I.OBJECT_NAME || '"');
END LOOP;
FOR I IN ( SELECT OBJECT_NAME
, OBJECT_TYPE
FROM USER_OBJECTS
WHERE OBJECT_TYPE = 'SEQUENCE') LOOP
EXECUTE IMMEDIATE ('DROP '|| I.OBJECT_TYPE || ' "' || I.OBJECT_NAME || '"');
END LOOP;
FOR I IN ( SELECT OBJECT_NAME
, OBJECT_TYPE
FROM USER_OBJECTS
WHERE OBJECT_TYPE = 'TABLE') LOOP
EXECUTE IMMEDIATE ('DROP '|| I.OBJECT_TYPE || ' "' || I.OBJECT_NAME || '" CASCADE CONSTRAINTS');
END LOOP;
FOR I IN ( SELECT object_name
FROM user_objects
WHERE object_type = 'TYPE'
ORDER BY object_id DESC) LOOP
EXECUTE IMMEDIATE ('DROP TYPE "' || I.OBJECT_NAME ||'"');
dbms_output.put_line(I.OBJECT_NAME);
END LOOP;
END;
No comments:
Post a Comment