--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);
		CURSOR c_tables IS
			SELECT 'emp' table_name FROM dual
				UNION
			SELECT 'dept' table_name FROM dual
		;
BEGIN
-- extend the window
   BEGIN
	-- get the handle
	     SELECT handle INTO vSubhandle FROM all_subscriptions 
		     WHERE description = v_subscription_description;
	-- Extend the window for subscription
	     DBMS_CDC_SUBSCRIBE.EXTEND_WINDOW(SUBSCRIPTION_HANDLE=>vSubhandle);
	     DBMS_output.put_line('Window to the CDC subscription '''|| v_subscription_description
			|| ''' was successfully extended');
     EXCEPTION
	WHEN OTHERS THEN
		DBMS_output.put_line('Error start *************************************');
		DBMS_output.put_line('Error during window extention to CDC subscription '''
			||v_subscription_description || ''':');
		DBMS_output.put_line('Handle # '||TO_CHAR(vSubhandle));
		DBMS_output.put_line(to_char(sqlcode)|| ' ' || sqlerrm);
		DBMS_output.put_line('Error end   *************************************');
   END;
-- create subscriber views/synonyms
   BEGIN
	FOR tables IN c_tables LOOP
	   -- create a view
	   v_source_table := UPPER(TRIM(tables.table_name));
	   BEGIN
	      v_cdc_table := 'CDC_'||v_source_table; v_cdc_view_name:=v_cdc_table || '_vw';
		DBMS_CDC_SUBSCRIBE.PREPARE_SUBSCRIBER_VIEW(
			SUBSCRIPTION_HANDLE=> vSubhandle
			,SOURCE_SCHEMA => v_source_schema,SOURCE_TABLE => v_source_table
			,VIEW_NAME => our_view_name);
	      DBMS_output.put_line('Subscriber view '''||our_view_name
		|| ''' was successfully created for table '||v_source_schema||'.'||v_source_table);
  	    EXCEPTION
		WHEN OTHERS THEN
		  DBMS_output.put_line('Error start *************************************');
		  DBMS_output.put_line('Error during creation of subscriber view for source table '
				||v_source_table||': ');
		  DBMS_output.put_line(to_char(sqlcode)|| ' ' || sqlerrm||'  Handle # '||TO_CHAR(vSubhandle));
		  DBMS_output.put_line('Error end   *************************************');
	   END;
	   -- drop the previous synonym
	   BEGIN
		vSQL := 'DROP SYNONYM ' || v_cdc_view_name;
		EXECUTE IMMEDIATE vSQL;
	        EXCEPTION WHEN OTHERS THEN NULL;
	   END;
	   -- create a private synonym to point to this view:
	   BEGIN
		vSQL := 'CREATE SYNONYM ' || v_cdc_view_name ||' FOR '|| our_view_name;
		EXECUTE IMMEDIATE vSQL;
		DBMS_output.put_line('Private synonym ''' || v_cdc_view_name 
			|| ''' for view ''' || our_view_name || ''' was successfully created.');
	        EXCEPTION
		  WHEN OTHERS THEN
			DBMS_output.put_line('Error start ******************************');
			DBMS_output.put_line('Error during creation of the synonym '''
				|| v_cdc_view_name || ''' for view '''|| our_view_name || ''': ');
			DBMS_output.put_line(to_char(sqlcode)|| ' ' || sqlerrm);
			DBMS_output.put_line('Error end   ******************************');
	   END;
	END LOOP;
   END;
END;
END;
/