--connect boris_subscriber/boris_subscriber@whatever
set serveroutput on size 1000000
set linesize 200
/* Login as a subscriber to run this */
BEGIN
	DECLARE vSubhandle NUMBER;
		v_subscription_description VARCHAR2(40):='scott -> Datawarehouse';
		v_publisher_id VARCHAR2(20) := 'BORIS_PUBLISHER';
		v_source_schema VARCHAR2(20) := 'SCOTT';
		v_subscriber_id VARCHAR2(20) := 'BORIS_SUBSCRIBER';
		v_source_table VARCHAR2(100);
		v_cdc_table VARCHAR2(100);
		v_cdc_view_name VARCHAR2(40);
		our_view_name VARCHAR2(200);
		vSQL VARCHAR2(1000);
		v_err VARCHAR2(10):='OK';
		CURSOR c_tables IS
			SELECT 'emp' table_name FROM dual
				UNION
			SELECT 'dept' table_name FROM dual
		;
BEGIN
-- find a subscription
   	BEGIN
	     vSubhandle :=0;
	     -- get the handle
	     SELECT handle INTO vSubhandle FROM all_subscriptions 
		     WHERE description = v_subscription_description;
	  EXCEPTION
		WHEN OTHERS THEN
		   vSubhandle := 0;
		   dbms_output.put_line('Subscription '||v_subscription_description 
			|| ' does not exist in the database'
		   );
	END;
	FOR tables IN c_tables LOOP
	   v_source_table := UPPER(TRIM(tables.table_name));
	   BEGIN
		v_cdc_table := 'CDC_'||v_source_table;
		v_cdc_view_name:=v_cdc_table || '_vw';
		-- drop the synonym
		BEGIN
		   vSQL := 'DROP SYNONYM ' || v_cdc_view_name;
		   EXECUTE IMMEDIATE vSQL;
		  EXCEPTION WHEN OTHERS THEN NULL;
		END;
		-- drop the subscriber view
	        DBMS_CDC_SUBSCRIBE.DROP_SUBSCRIBER_VIEW(
			SUBSCRIPTION_HANDLE=> vSubhandle,SOURCE_SCHEMA => v_source_schema
			, SOURCE_TABLE => v_source_table);
		DBMS_output.put_line('Subscriber View for CDC table ''' 
		|| v_cdc_table || ''' was successfully dropped.  Handle # '||TO_CHAR(vSubhandle));
	      EXCEPTION
		WHEN OTHERS THEN
		DBMS_output.put_line('Error start ********************************');
		DBMS_output.put_line('Error during dropping the subscriber view for ''' 
			|| v_cdc_table || '''  '
			|| ' to the CDC subscription ''' || v_subscription_description || ''':');
		DBMS_output.put_line(to_char(sqlcode)|| ' ' || sqlerrm 
			|| '  Handle = '||TO_CHAR(vSubhandle));
		DBMS_output.put_line('Error end   ********************************');
	   END;
	END LOOP;
  BEGIN
	-- purge window
	DBMS_CDC_SUBSCRIBE.PURGE_WINDOW(SUBSCRIPTION_HANDLE=> vSubhandle);
	DBMS_output.put_line('Subscriber Window for subscription ''' 
		|| v_subscription_description || ''' was successfully purged ');	
     EXCEPTION
	WHEN OTHERS THEN
	  DBMS_output.put_line('Error during subscriber view drop: ');
	  DBMS_output.put_line(to_char(sqlcode)|| ' ' || sqlerrm
		||'  Handle # '||TO_CHAR(vSubhandle));
  END;
END;
END;
/