Oracle-to-Oracle Database Resynchronization Script

drop table rs_info; 
commit;
 
create table rs_info (rskey varchar2 (20), rsval varchar2 (20));
commit;

insert into rs_info values ('charset_name', 'iso_1');
commit;
 
insert into rs_info values ('sortorder_name', 'bin_iso_1'); 
commit; 

drop public synonym rs_lastcommit;
commit;

drop table rs_lastcommit;
commit;
 
create table rs_lastcommit( 
		origin number(8),origin_qid char(72 byte),
		secondary_qid char(72 byte),  
		origin_time date,
		dest_commit_time date,
		conn_id number default 0);
commit;

grant all on rs_lastcommit to public;
commit;

create public synonym rs_lastcommit for rs_lastcommit;
commit;

CREATE OR REPLACE PROCEDURE RS_UPDATE_SEQUENCE
(
	SequenceName VARCHAR2,
	SequenceValue NUMBER,
	Increment NUMBER
) 
AS 
	CurrentID NUMBER; 
	LastID NUMBER; 
	SeqCursor INTEGER; 
	SQLStmt VARCHAR2(1024); 
	Result NUMBER;
BEGIN 
	SQLStmt := 'SELECT ' || SequenceName || '.NEXTVAL FROM DUAL'; 
	SeqCursor := DBMS_SQL.OPEN_CURSOR;DBMS_SQL.PARSE(SeqCursor,
							 SQLStmt,
							 DBMS_SQL.NATIVE);
	DBMS_SQL.DEFINE_COLUMN(SeqCursor, 
				   1, 
				   LastID); 
	Result := DBMS_SQL.EXECUTE_AND_FETCH(SeqCursor); 
	DBMS_SQL.COLUMN_VALUE(SeqCursor,1,CurrentID);
	LOOP 
		IF ( Increment < 0 ) THEN 
			EXIT WHEN CurrentID <= SequenceValue;  
			EXIT WHEN CurrentID > LastID; 
		ELSE  
			EXIT WHEN CurrentID >= SequenceValue;
			EXIT WHEN CurrentID < LastID; 
		END IF;
		LastID := CurrentID; 
		Result := DBMS_SQL.EXECUTE_AND_FETCH(SeqCursor); 
		DBMS_SQL.COLUMN_VALUE(SeqCursor,1,CurrentID);
	END LOOP;
	DBMS_SQL.CLOSE_CURSOR(SeqCursor);
END; 
/

grant all on RS_UPDATE_SEQUENCE to public;
commit;

DROP sequence rs_ticket_seq;

CREATE sequence rs_ticket_seq;

Drop table rs_ticket_history;

CREATE TABLE rs_ticket_history (
		cnt numeric(8,0), 
		h1 varchar(10), 
		h2 varchar(10), 
		h3 varchar(10), 
		h4 varchar(50), 
		pdb varchar(30), 
		prs varchar(30), 
		rrs varchar(30), 
		rdb varchar(30), 
		pdb_t varchar(128), 
		exec_t varchar(128), 
		dist_t varchar(128), 
		rsi_t varchar(128), 
		dsi_t varchar(128), 
		rdb_t varchar(128) default current_date, 
		exec_b numeric(22,0), 
		rsi_b numeric(22,0), 
		dsi_tnx numeric(22,0), 
		dsi_cmd numeric(22,0), 
		ticket varchar(1024),  
		conn_id number NULL); 
	 
create unique index rs_ticket_idx on rs_ticket_history(cnt); 
 
create or replace trigger rs_ticket_tri  
before insert on rs_ticket_history  for each row  
begin  
	if :new.cnt  is null then 
		select rs_ticket_seq.nextval into :new.cnt  from dual; 
	end if;
end rs_ticket_tri;
/

grant all on rs_ticket_history to public;
commit;

create or replace package RS_TRIGGER_CONTROL 
as 
	procedure ENABLE; 
	function IS_ENABLED 
		return boolean; 
	end; 
/

create or replace package body RS_TRIGGER_CONTROL 
as 
	fire_trigger   boolean := TRUE; 
	function IS_ENABLED return boolean as 
	begin 
		if fire_trigger then 
			return FALSE;  
		else 
			return TRUE;  
		end if; 
	end IS_ENABLED; 
 
procedure ENABLE  
as  
	begin  
		fire_trigger := FALSE; 
	end ENABLE;  
	 
procedure DISABLE  
as 
	begin  
		fire_trigger := TRUE;  
	end DISABLE;  
end RS_TRIGGER_CONTROL; 
/ 
 
grant all on rs_trigger_control to public;
 
drop public SYNONYM rs_trigger_control;
 
create public SYNONYM rs_trigger_control for rs_trigger_control;
commit; 
 
CREATE OR REPLACE PROCEDURE rs_send_repserver_cmd(rs_api IN VARCHAR2) 
AS  
	cmd VARCHAR2 (4000); 
	plsql_block VARCHAR2(50);  
	BEGIN 
		if (instr(lower(rs_api), 'rs_rcl') > 0) then  
			DBMS_OUTPUT.put_line('The Replication Server command should not contain the keyword ''rs_rcl''');  
			return;  
		end if;  
		cmd := 'rs_rcl ''' ||  replace(rs_api, '''', '''''')  || ''' rs_rcl';  
		if ('rs_rcl' !=  substr(cmd, length(cmd) - 5)) then 
			DBMS_OUTPUT.put_line('The Replication Server command is too long.');   
			DBMS_OUTPUT.put_line('Please split it into two or more commands');  
			return;  
		end if;  
		plsql_block := 'begin rs_marker(:a); end;';  
		EXECUTE IMMEDIATE plsql_block USING cmd;  
	END;  
/ 
 
drop public SYNONYM rs_send_repserver_cmd; 
 
create public SYNONYM rs_send_repserver_cmd for rs_send_repserver_cmd;
commit;