--connect boris_subscriber/boris_subscriber@whatever

set serveroutput on size 1000000
set linesize 120

/* 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);
		col_names VARCHAR2(32767);
		v_err VARCHAR2(10):='OK';
		CURSOR c_tables IS
			SELECT 'emp' table_name FROM dual
				UNION
			SELECT 'dept' table_name FROM dual
		;
BEGIN
-- find/create 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;

	BEGIN
	     IF (vSubhandle = 0) THEN
		     -- create handle
			DBMS_CDC_SUBSCRIBE.GET_SUBSCRIPTION_HANDLE(
				CHANGE_SET => 'SYNC_SET', 
				DESCRIPTION => v_subscription_description,
				SUBSCRIPTION_HANDLE => vSubhandle)
			; 
		dbms_output.put_line('Obtained handle '||TO_CHAR(vSubhandle) 
			|| ' for subscription '||v_subscription_description 
		);
	     END IF;
	   EXCEPTION
		WHEN OTHERS THEN
		DBMS_output.put_line('Error start ********************************************');
		DBMS_output.put_line('Error getting a handle '
			|| ' for a CDC subscription ' || v_subscription_description || ':');
		DBMS_output.put_line(to_char(sqlcode)|| ' ' || sqlerrm || '  Handle = '||TO_CHAR(vSubhandle));
		DBMS_output.put_line('Error end   ********************************************');
		v_err :='ERROR';
	END;
-- Subscribe for all the tables
	FOR tables IN c_tables LOOP
	   v_source_table := UPPER(TRIM(tables.table_name));
	   BEGIN
	      IF (v_err = 'ERROR') THEN RETURN; END IF;
	      v_cdc_table := 'CDC_'||v_source_table;
	      col_names := etl_util.get_col_names(v_source_schema,v_source_table, '','') ;
	      DBMS_LOGMNR_CDC_SUBSCRIBE.SUBSCRIBE (vSubhandle, v_source_schema,
                       v_source_table, col_names);
		DBMS_output.put_line('Added table '|| v_source_schema ||'.'|| v_source_table 
		|| ' to the CDC subscription ''' || v_subscription_description 
		|| ''' successfully. Handle = '||TO_CHAR(vSubhandle));
	   EXCEPTION
		WHEN OTHERS THEN
		DBMS_output.put_line('Error start ********************************************');
		DBMS_output.put_line('Error adding table '|| v_source_schema||'.'||v_source_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   ********************************************');
		v_err :='ERROR';
	   END;
	END LOOP;
	BEGIN 
-- Activate the subscription
	   IF (v_err = 'ERROR') THEN RETURN; END IF;
	   DBMS_CDC_SUBSCRIBE.ACTIVATE_SUBSCRIPTION(vSubhandle);
	   DBMS_output.put_line('CDC subscription '''|| v_subscription_description 
||''' was successfully activated. Handle = '||TO_CHAR(vSubhandle));
   	   EXCEPTION
		WHEN OTHERS THEN
		DBMS_output.put_line('Error start ********************************************');
		DBMS_output.put_line('Error activating the CDC subscription '''
|| v_subscription_description || ''':');
		DBMS_output.put_line(to_char(sqlcode)|| ' ' || sqlerrm 
|| '  Handle = '||TO_CHAR(vSubhandle));
		DBMS_output.put_line('Error end   ********************************************');
		v_err :='ERROR';
	END;
-- Extend the window immediately 
	BEGIN
	-- Extend the window for subscription
		IF (v_err = 'ERROR') THEN RETURN; END IF;
	        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   ********************************************');
		v_err :='ERROR';
   	END;

END;
END;
/