--connect boris_publisher/boris_publisher@whatever
set serveroutput on size 1000000
set linesize 120

/* Login as a publisher to run this */
BEGIN
	DECLARE work_sql VARCHAR2(32767);
		v_publisher_id VARCHAR2(20)  := 'boris_publisher';
		v_source_schema VARCHAR2(20) := 'scott';
		v_source_table VARCHAR2(100);
		v_cdc_table VARCHAR2(100);
		CURSOR c_tables IS
			SELECT 'emp' table_name FROM dual
				UNION
			SELECT 'dept' table_name FROM dual
		;
BEGIN
 FOR tables IN c_tables LOOP
   v_source_table := tables.table_name;
   v_cdc_table := 'CDC_'||v_source_table;
   work_sql := etl_util.get_cols_definition(
		p_schema=>v_source_schema, 
		p_table_name =>v_source_table, 
		p_skip_columns => NULL,
		p_pref => NULL);
   DBMS_LOGMNR_CDC_PUBLISH.CREATE_CHANGE_TABLE (
	OWNER => v_publisher_id, SOURCE_SCHEMA => v_source_schema
	,CHANGE_SET_NAME => 'SYNC_SET', CAPTURE_VALUES => 'both'
	,RS_ID => 'y', ROW_ID => 'n', USER_ID => 'y', TIMESTAMP => 'y'
	,OBJECT_ID => 'n' -- leave it as 'N' or you will have "table has no columns" error 
	,SOURCE_COLMAP => 'y', TARGET_COLMAP => 'y', OPTIONS_STRING => null
	,SOURCE_TABLE => v_source_table, CHANGE_TABLE_NAME => v_cdc_table
	,COLUMN_TYPE_LIST => work_sql);
   DBMS_output.put_line('Change table '||v_cdc_table ||' was created successfully');
 END LOOP;

EXCEPTION
	WHEN OTHERS THEN
	DBMS_output.put_line('Error start ********************************************');
	DBMS_output.put_line('Error during change table ' || v_cdc_table || ' creation:');
	DBMS_output.put_line(to_char(sqlcode)|| ' ' || sqlerrm);
	DBMS_output.put_line('Error end   ********************************************');
END;
END;
/