set serveroutput on size 100000
set verify off
spool drop_user.log
PROMPT
ACCEPT dropschema PROMPT 'Schema waarvan de objecten leeggemaakt moeten worden: '
PROMPT
declare
 w_p_string varchar2(254);
 w_cursor_id integer;
 w_p_exec integer;
cursor cur1 is
select object_type, object_name
from dba_objects
where owner = upper('&&dropschema')
  and object_type in ('CLUSTER', 'TABLE', 'VIEW', 'SEQUENCE', 'SYNONYM',
                      'FUNCTION',
                      'PROCEDURE', 'PACKAGE');
cur1rec cur1%ROWTYPE;

begin
   w_cursor_id:=dbms_sql.open_cursor;
   FOR cur1rec IN cur1 LOOP
     BEGIN
       w_p_string := 'drop '||cur1rec.object_type||' &&dropschema'||'.'||cur1rec.object_name;
    if cur1rec.object_type = 'CLUSTER'
      then w_p_string := w_p_string||' including tables cascade constraints';
    elsif cur1rec.object_type = 'TABLE'
      then w_p_string := w_p_string||' cascade constraints';
    else
            w_p_string := w_p_string||'';
       end if;
       dbms_output.put_line(w_p_string);
       dbms_sql.parse(w_cursor_id,w_p_string,1);
       w_p_exec := dbms_sql.execute(w_cursor_id);
     EXCEPTION WHEN others THEN
        dbms_output.put_line('Error Processing '
                           ||cur1rec.object_type||'.'||cur1rec.object_name);
        dbms_output.put_line(SQLERRM);
        dbms_output.put_line('.');
     END;
   END LOOP;
   dbms_output.put_line('.');
   dbms_output.put_line('Proces is afgerond');
   dbms_output.put_line('.');
   dbms_sql.close_cursor(w_cursor_id);
end;
/
spool off;
PROMPT Sluit sqlplus mbv exit