DECLARE
TYPE NAME_LIST IS TABLE OF VARCHAR2(40);
TYPE TYPE_LIST IS TABLE OF VARCHAR2(20);
TAB_NAME NAME_LIST := NAME_LIST();
TAB_TYPE TYPE_LIST := TYPE_LIST();
SQL_STR VARCHAR2(500);
BEGIN
SQL_STR := 'SELECT UO.OBJECT_NAME,UO.OBJECT_TYPE FROM USER_OBJECTS UO WHERE UO.OBJECT_TYPE NOT IN(''INDEX'',''LOB'') AND UO.OBJECT_NAME NOT LIKE ''BIN%'' ORDER BY UO.OBJECT_TYPE DESC';
EXECUTE IMMEDIATE SQL_STR BULK COLLECT
INTO TAB_NAME, TAB_TYPE;
FOR I IN TAB_NAME.FIRST .. TAB_NAME.LAST LOOP
IF TAB_TYPE(I) = 'TABLE' THEN
SQL_STR := 'DROP ' || TAB_TYPE(I) || ' ' || TAB_NAME(I) ||
' CASCADE CONSTRAINT';
ELSE
SQL_STR := 'DROP ' || TAB_TYPE(I) || ' ' || TAB_NAME(I);
END IF;
DBMS_OUTPUT.PUT_LINE(SQL_STR);
EXECUTE IMMEDIATE SQL_STR;
END LOOP;
END ;