
undefine action
undefine owner

set verify off
set linesize 160
set pagesize 30
set pause on
set pause '....hit <ENTER> to continue'

spool dropScript

prompt 
prompt ********************************************************************
prompt This script will drop all schema for the specified owner in the
prompt database you are connected to.
prompt
prompt You can run this for real or just review the generated code.
prompt 
prompt You will also need to have the correct Oracle permissions or this 
prompt will not work.
prompt ********************************************************************
prompt
accept action prompt 'Do you want to process or review this (process/review): '
accept owner prompt 'Enter the schema owner: '

set serveroutput on size 1000000 format word_wrapped
DECLARE

status BOOLEAN;

FUNCTION run_sql ( rs_action	 IN VARCHAR2
        	  ,rs_command IN VARCHAR2)
  RETURN BOOLEAN AS status BOOLEAN;

  rs_ignore_for_ddl INTEGER;
  rs_ddl_cursor_id  INTEGER;

BEGIN

    -- Display the command to be executed
    DBMS_OUTPUT.PUT_LINE( '-  -	  -  -	-  -  -	 -  -  -  -  -	-');
    DBMS_OUTPUT.PUT_LINE (rs_command);

    status := TRUE;
    IF rs_action = 'process' THEN
	-- allocate a cursor to use when executing DLL commands
      rs_ddl_cursor_id := DBMS_SQL.OPEN_CURSOR;

      -- Parse and execute the command
	BEGIN
	DBMS_SQL.PARSE(rs_ddl_cursor_id, rs_command, DBMS_SQL.NATIVE);
	DBMS_OUTPUT.PUT_LINE( '***SUCCESSFUL***');
	status := TRUE;
	EXCEPTION
	WHEN OTHERS THEN
	  DBMS_OUTPUT.PUT_LINE(SQLERRM(SQLCODE));
	  DBMS_OUTPUT.PUT_LINE( '***FAILED***');
	  status := FALSE;
	END;

      -- Deallocate the cursor
	rs_ignore_for_ddl := DBMS_SQL.EXECUTE(rs_ddl_cursor_id);
	DBMS_SQL.CLOSE_CURSOR(rs_ddl_cursor_id);
    END IF; -- end if status

RETURN status;
END run_sql;
-- -----------------------------------------------------------------
FUNCTION deleteSequences(ds_action in VARCHAR2, 
                          ds_owner in VARCHAR2 )
  RETURN BOOLEAN as status BOOLEAN;

  TYPE table_cur IS REF CURSOR;
  table_rec	table_cur;

  wkName      VARCHAR2(30);
  sql_stmt    VARCHAR2(500);
  ddl_command VARCHAR2(500);
BEGIN
  sql_stmt := 'select sequence_name from all_sequences ' ||
             'where sequence_owner = ' || '''' || ds_owner || '''';
  OPEN table_rec FOR sql_stmt;
  LOOP
    FETCH table_rec INTO wkName;
    EXIT WHEN table_rec%NOTFOUND;
    ddl_command := 'DROP  SEQUENCE ' || ds_owner || '.' || wkName ;
    status :=  run_sql ( ds_action, ddl_command); 
   END LOOP;
  CLOSE table_rec;
  RETURN status;
END deleteSequences;
-- -----------------------------------------------------------------
FUNCTION deleteTables(ds_action in VARCHAR2, 
                       ds_owner in VARCHAR2 )
  RETURN BOOLEAN as status BOOLEAN;


  TYPE table_cur IS REF CURSOR;
  table_rec	table_cur;
  wkName      VARCHAR2(30);
  sql_stmt    VARCHAR2(500);
  ddl_command VARCHAR2(500);
BEGIN
  sql_stmt := 'select table_name from all_tables ' ||
             'where owner = ' || '''' || ds_owner || '''';
  OPEN table_rec FOR sql_stmt;
  LOOP
    FETCH table_rec INTO wkName;
    EXIT WHEN table_rec%NOTFOUND;
    ddl_command := 'DROP  TABLE ' || ds_owner || '.' || wkName ;
    status :=  run_sql ( ds_action, ddl_command); 
   END LOOP;
  CLOSE table_rec;
  RETURN status;
END deleteTables;

-- -----------------------------------------------------------------
FUNCTION deleteViews(ds_action in VARCHAR2, 
                      ds_owner in VARCHAR2 )
  RETURN BOOLEAN as status BOOLEAN;


  TYPE table_cur IS REF CURSOR;
  table_rec	table_cur;
  wkName      VARCHAR2(30);
  sql_stmt    VARCHAR2(500);
  ddl_command VARCHAR2(500);
BEGIN
  sql_stmt := 'select view_name from all_views ' ||
             'where owner = ' || '''' || ds_owner || '''';
  OPEN table_rec FOR sql_stmt;
  LOOP
    FETCH table_rec INTO wkName;
    EXIT WHEN table_rec%NOTFOUND;
    ddl_command := 'DROP  VIEW ' || ds_owner || '.' || wkName ;
    status :=  run_sql ( ds_action, ddl_command); 
   END LOOP;
  CLOSE table_rec;
  RETURN status;
END deleteViews;

-- -----------------------------------------------------------------
FUNCTION deleteSynonyms(ds_action in VARCHAR2, 
                        ds_owner in VARCHAR2 )
  RETURN BOOLEAN as status BOOLEAN;


  TYPE table_cur IS REF CURSOR;
  table_rec	table_cur;
  wkName      VARCHAR2(30);
  sql_stmt    VARCHAR2(500);
  ddl_command VARCHAR2(500);
BEGIN
  sql_stmt := 'select synonym_name from all_synonyms  ' ||
             'where table_owner = ' || '''' || ds_owner || '''';
  OPEN table_rec FOR sql_stmt;
  LOOP
    FETCH table_rec INTO wkName;
    EXIT WHEN table_rec%NOTFOUND;
    ddl_command := 'DROP PUBLIC SYNONYM ' ||  wkName ;
    status :=  run_sql ( ds_action, ddl_command); 
   END LOOP;
  CLOSE table_rec;
  RETURN status;
END deleteSynonyms;
-- -----------------------------------------------------------------
BEGIN

dbms_output.put_line('***********  Dropping Sequences  ***********'); 
status := deleteSequences(lower('&&action'),upper('&&owner'));
dbms_output.put_line('***********  Dropping Tables  ***********');
status := deleteTables(lower('&&action'),upper('&&owner'));
status := deleteTables(lower('&&action'),upper('&&owner'));
status := deleteTables(lower('&&action'),upper('&&owner'));
dbms_output.put_line('***********  Dropping Views  ***********');
status := deleteViews(lower('&&action'),upper('&&owner'));
dbms_output.put_line('***********  Dropping Synonyms  ***********'); 
status := deleteSynonyms(lower('&&action'),upper('&&owner'));

END;

/
set serveroutput off

prompt
prompt ******************************************************
prompt The following display contains all the objects owned
prompt by &&owner.  It the deletes were successful, there
prompt should be no rows selected.
prompt
prompt select owner, object_name, object_type, created
prompt from all_objects where owner = '&&owner';
prompt

set sqlcase upper
select owner, object_name, object_type, created
from all_objects where owner = '&&owner';
set sqlcase mixed

spool off

undefine action
undefine owner

set verify on
