For this kind of thing, I like to keep a separate table of what I think the remote database looks like. That way, I can:
- Generate deltas easily by comparing my source data with my copy of what should be in the remote database.
- Correct errors in PROD by updating the copy to force the process to resend (e.g., if the team managing the other database misses a file or something).
Here is a working example to illustrate the process.
Cast of characters:
SO_SERVICES --> your source tableSO_SERVICES_EXPORTED --> a copy of what the remote database should currently look like, if they've processed all our command .csv files correctly.SO_SERVICES_EXPORT_CMDS --> the set of deltas generated by comparing SO_SERVICES and SO_SERVICES_EXPORTED. You would generate your .csv file from this table and then delete from it.SYNC_SERVICES --> a procedure to generate the deltas
Setup Tables
CREATE TABLE so_services
( service_id NUMBER NOT NULL,
id_service_provider NUMBER NOT NULL,
valid_from DATE NOT NULL,
valid_to DATE DEFAULT DATE '9999-12-31' NOT NULL,
CONSTRAINT so_services_pk PRIMARY KEY ( service_id, id_service_provider ),
CONSTRAINT so_services_c1 CHECK ( valid_from <= valid_to ) );
CREATE TABLE so_services_exported
( service_id NUMBER NOT NULL,
id_service_provider NUMBER NOT NULL,
valid_from DATE NOT NULL,
valid_to DATE DEFAULT DATE '9999-12-31' NOT NULL,
CONSTRAINT so_services_exported_pk PRIMARY KEY ( service_id ),
CONSTRAINT so_services_exported_c1 CHECK ( valid_from <= valid_to ) );
CREATE TABLE so_services_export_cmds
( service_id NUMBER NOT NULL,
id_service_provider NUMBER,
cmd VARCHAR2(30) NOT NULL,
valid_from DATE,
valid_to DATE,
CONSTRAINT so_services_export_cmds_pk PRIMARY KEY ( service_id, cmd ) );
Procedure to process synchronization
-- You would put this in a package, for real code
CREATE OR REPLACE PROCEDURE sync_services IS
BEGIN
LOCK TABLE so_services IN EXCLUSIVE MODE;
-- Note the deltas between the current active services and what we've exported so far
-- CAVEAT: I am not sweating your exact business logic here. I am just trying to illustrate the approach.
-- The logic here assumes that the target database wants only one row for each service_id, so we will send an
-- "ADD" if the target database should insert a new service ID, "UPDATE", if it should modify an existing service ID,
-- or "DELETE" if it should delete it.
-- Also assuming, for "DELETE" command, we only need the service_id, no other fields.
INSERT INTO so_services_export_cmds
( service_id, id_service_provider, cmd, valid_from, valid_to )
SELECT nvl(so.service_id, soe.service_id) service_id,
so.id_service_provider id_service_provider,
CASE WHEN so.service_id IS NOT NULL AND soe.service_id IS NULL THEN 'ADD'
WHEN so.service_id IS NULL AND soe.service_id IS NOT NULL THEN 'DELETE'
WHEN so.service_id IS NOT NULL AND soe.service_id IS NOT NULL THEN 'UPDATE'
ELSE NULL -- this will fail and should.
END cmd,
so.valid_from valid_from,
so.valid_to valid_to
FROM ( SELECT * FROM so_services WHERE SYSDATE BETWEEN valid_from AND valid_to ) so
FULL OUTER JOIN so_services_exported soe ON soe.service_id = so.service_id
-- Exclude any UPDATES that don't change anything
WHERE NOT ( soe.service_id IS NOT NULL
AND so.service_id IS NOT NULL
AND so.id_service_provider = soe.id_service_provider
AND so.valid_from = soe.valid_from
AND so.valid_to = soe.valid_to);
-- Update the snapshot of what the remote database should now look like after processing the above commands.
-- (i.e., it should have all the current records from the service table)
DELETE FROM so_services_exported;
INSERT INTO so_services_exported
( service_id, id_service_provider, valid_from, valid_to )
SELECT service_id, id_service_provider, valid_from, valid_to
FROM so_services so
WHERE SYSDATE BETWEEN so.valid_from AND so.valid_to;
-- For testing (12c only)
DECLARE
c SYS_REFCURSOR;
BEGIN
OPEN c FOR SELECT * FROM so_services_export_cmds ORDER BY service_id;
DBMS_SQL.RETURN_RESULT(c);
END;
COMMIT; -- Release exclusive lock on services table
END sync_services;
Insert Test Data from OP
DELETE FROM so_services;
INSERT INTO so_services ( service_id, id_service_provider, valid_from, valid_to )
VALUES ( 114, 20, DATE '2011-12-06', DATE '2017-10-16' );
INSERT INTO so_services ( service_id, id_service_provider, valid_from, valid_to )
VALUES ( 211, 65, DATE '2015-05-09', DATE '9999-12-31' );
INSERT INTO so_services ( service_id, id_service_provider, valid_from, valid_to )
VALUES ( 322, 57, DATE '2019-08-22', DATE '9999-12-31' );
INSERT INTO so_services ( service_id, id_service_provider, valid_from, valid_to )
VALUES ( 336, 21, DATE '2009-08-20', DATE '2019-07-11' );
INSERT INTO so_services ( service_id, id_service_provider, valid_from, valid_to )
VALUES ( 336, 37, DATE '2019-07-11', DATE '9999-12-31' );
Test #1 -- Nothing exported yet, so all latest records should be sent
exec sync_services;
SERVICE_ID ID_SERVICE_PROVIDER CMD VALID_FRO VALID_TO
---------- ------------------- ------------------------------ --------- ---------
211 65 ADD 09-MAY-15 31-DEC-99
322 57 ADD 22-AUG-19 31-DEC-99
336 37 ADD 11-JUL-19 31-DEC-99
Test #2 -- no additional updates, no additional commands
DELETE FROM so_services_export_cmds; -- You would do this after generating your .csv file
exec sync_services;
no rows selected
Test #3 - Add some changes to the source table
-- Add a new service #400
INSERT INTO so_services ( service_id, id_service_provider, valid_from, valid_to )
VALUES ( 400, 20, DATE '2019-08-29', DATE '9999-12-31' );
-- Terminate service 322
UPDATE so_services
SET valid_to = DATE '2019-08-29'
WHERE service_id = 322
AND valid_to = DATE '9999-12-31';
-- Update service 336
UPDATE so_services
SET valid_to = DATE '2019-08-29'
WHERE service_id = 336
AND id_service_provider = 37
AND valid_to = DATE '9999-12-31';
INSERT INTO so_services ( service_id, id_service_provider, valid_from, valid_to )
VALUES ( 336, 88, DATE '2019-08-29', DATE '9999-12-31' );
exec sync_services;
SERVICE_ID ID_SERVICE_PROVIDER CMD VALID_FRO VALID_TO
---------- ------------------- ------------------------------ --------- ---------
322 DELETE
336 88 UPDATE 29-AUG-19 31-DEC-99
400 20 ADD 29-AUG-19 31-DEC-99
