set serveroutput on
set feedback off
set verify off
clear screen

PROMPT
PROMPT Warning this script Drops the Template Builder WPTB user and Tablespaces!!!;
PROMPT;
ACCEPT V_ANSWER CHAR PROMPT 'Are you sure you want to continue? (Yes/No) '

declare

  v_answer char(3);
  wk_count number;

  Fail_Block EXCEPTION;

begin

  v_answer := '&&V_ANSWER';

  IF v_answer = 'Yes' THEN

        
        dbms_output.put_line(CHR(13));
        dbms_output.put_line('Processing started...');

      	dbms_output.put_line('Dropping the WPTB user...');

	-- Drop the WPTB user if it exists
	BEGIN

         SELECT COUNT(*)
         INTO wk_count
         FROM dba_users
         WHERE username = 'WPTB';


        IF wk_count = 1 THEN

	   -- Dropping the WPTB user
	   BEGIN

	      EXECUTE IMMEDIATE 'drop user WPTB cascade';

	   EXCEPTION 
	      WHEN OTHERS THEN
		   dbms_output.put_line(CHR(13));
		   dbms_output.put_line('ERROR: Failed dropping the WPTB user.');
		   RAISE Fail_Block;

           END;
	
	   dbms_output.put_line('The WPTB user dropped...');

	ELSIF wk_count = 0 THEN 

	   dbms_output.put_line('The WPTB user does not exist...Continuing processing.');

	ELSE

	   dbms_output.put_line('ERROR: Unexpected count of users.');
	   RAISE Fail_Block;

	END IF;

	dbms_output.put_line('Dropping the TEMPLATEBUILDER_DATA tablespace...');

      END;

	-- Drop the TEMPLATEBUILDER_DATA tablespace if it exists
	BEGIN

         SELECT COUNT(*)
         INTO wk_count
         FROM dba_tablespaces
         WHERE tablespace_name = 'TEMPLATEBUILDER_DATA';

      END;

	IF wk_count = 1 THEN

	   -- Dropping the TEMPLATEBUILDER_DATA tablespace
	   BEGIN

	      EXECUTE IMMEDIATE 'drop tablespace TEMPLATEBUILDER_DATA 
			 	       including contents and datafiles cascade constraints';

	   EXCEPTION 
	      WHEN OTHERS THEN
		   dbms_output.put_line(CHR(13));
		   dbms_output.put_line('ERROR: Failed dropping the TEMPLATEBUILDER_DATA tablespace.');
		   RAISE Fail_Block;

         END;
	
	   dbms_output.put_line('TEMPLATEBUILDER_DATA tablespace dropped...');

	ELSIF wk_count = 0 THEN 

	   dbms_output.put_line('The TEMPLATEBUILDER_DATA tablespace does not exist...Continuing processing.');

	ELSE

	   dbms_output.put_line('ERROR: Unexpected count of tablespaces.');
	   RAISE Fail_Block;

	END IF;

	dbms_output.put_line('Dropping the TEMPLATEBUILDER_INDEX tablespace...');

	-- Drop the TEMPLATEBUILDER_INDEX tablespace if it exists
	BEGIN

         SELECT COUNT(*)
         INTO wk_count
         FROM dba_tablespaces
         WHERE tablespace_name = 'TEMPLATEBUILDER_INDEX';

      END;

      IF wk_count = 1 THEN

	   -- Dropping the TEMPLATEBUILDER_INDEX tablespace
	   BEGIN

	      EXECUTE IMMEDIATE 'drop tablespace TEMPLATEBUILDER_INDEX 
			 	       including contents and datafiles cascade constraints';

	   EXCEPTION 
	      WHEN OTHERS THEN
		   dbms_output.put_line(CHR(13));
		   dbms_output.put_line('ERROR: Failed dropping the TEMPLATEBUILDER_INDEX tablespace.');
		   RAISE Fail_Block;

         END;
	
	   dbms_output.put_line('TEMPLATEBUILDER_INDEX tablespace dropped...');

	ELSIF wk_count = 0 THEN 

	   dbms_output.put_line('The TEMPLATEBUILDER_INDEX tablespace does not exist...Continuing processing.');

	ELSE

	   dbms_output.put_line('ERROR: Unexpected count of tablespaces.');
	   RAISE Fail_Block;

	END IF;

        dbms_output.put_line(CHR(13));
	dbms_output.put_line('Processing Completed Successfully!!!');
	dbms_output.put_line(CHR(13));

  Elsif v_answer = 'No' THEN

	dbms_output.put_line(CHR(13));
	dbms_output.put_line('No work performed.');
	
	RAISE Fail_Block;

  Else

	dbms_output.put_line(CHR(13));
	dbms_output.put_line('Invalid option. No work performed.');
	
	RAISE Fail_Block;

  End if;

EXCEPTION
   WHEN Fail_Block THEN

	dbms_output.put_line(CHR(13));
	dbms_output.put_line('Processing stopped!!');
	dbms_output.put_line(CHR(13));
	dbms_output.put_line('Processing failed for the following reasons:');
	dbms_output.put_line(CHR(13));

	
	If v_answer = 'Yes' Then

	   RAISE;
	   dbms_output.put_line(CHR(13));

	ElsIF v_answer = 'No' Then

	   dbms_output.put_line('User specified cancel.');
	   dbms_output.put_line(CHR(13));

	Else

	   dbms_output.put_line('Invalid option specified.');
	   dbms_output.put_line(CHR(13));

	End If;

end;
/

set feedback on