11-5. Synchronizing Data with a Remote Data Source
Problem
Your database instance requires data that is readily available in another Oracle instance but cannot be synchronized with a materialized view, and you do not want to duplicate data entry.
Solution
Write a procedure that creates a connection to the remote HR database and performs the steps needed to synchronize the two databases. Then use the EXEC DBMS_SCHEDULER.CREATE_JOB procedure to run the procedure on a regular basis. Suppose, for example, that your Oracle Database instance requires data from the HR employee table, which is in another instance. In addition, your employee table contains tables with foreign key references on the employee_id that prevents you from using a materialized view to keep the HR employee table in synchronization.
Create a database connection to the remote HR database, and then download the data on a regular basis:
CREATE DATABASE LINK hr_data
CONNECT TO hr
IDENTIFIED BY hr_password
USING '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=node_name)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=hr_service_name)))';
CREATE OR REPLACE PROCEDURE sync_hr_data AS
CURSOR driver IS
SELECT *
FROM employees@hr_data;
TYPE recs_type IS TABLE OF driver%ROWTYPE INDEX BY BINARY_INTEGER;
recs recs_type;
BEGIN
OPEN DRIVER;
FETCH DRIVER BULK COLLECT INTO recs;
CLOSE DRIVER;
FOR i IN 1..recs.COUNT LOOP
UPDATE employees
SET first_name = recs(i).first_name,
last_name = recs(i).last_name,
email = recs(i).email,
phone_number = recs(i).phone_number,
hire_date = recs(i).hire_date,
job_id = recs(i).job_id,
salary = recs(i).salary,
commission_pct = recs(i).commission_pct,
manager_id = recs(i).manager_id,
department_id = recs(i).department_id
WHERE employee_id = recs(i).employee_id
AND ( NVL(first_name,'~') <> NVL(recs(i).first_name,'~')
OR last_name <> recs(i).last_name
OR email <> recs(i).email
OR NVL(phone_number,'~') <> NVL(recs(i).phone_number,'~')
OR hire_date <> recs(i).hire_date
OR job_id <> recs(i).job_id
OR NVL(salary,-1) <> NVL(recs(i).salary,-1)
OR NVL(commission_pct,-1) <> NVL(recs(i).commission_pct,-1)
OR NVL(manager_id,-1) <> NVL(recs(i).manager_id,-1)
OR NVL(department_id,-1) <> NVL(recs(i).department_id,-1)
);
END LOOP;
-- find all new rows in the HR database since the last refresh
INSERT INTO employees
SELECT *
FROM employees@hr_data
WHERE employee_id NOT IN (
SELECT employee_id
FROM employees);
END sync_hr_data;
EXEC DBMS_SCHEDULER.CREATE_JOB ( -
JOB_NAME=>'sync_HR_employees', -
JOB_TYPE=>'STORED_PROCEDURE', -
JOB_ACTION=>'sync_hr_data', -
ENABLED=>TRUE, -
REPEAT_INTERVAL=>'FREQ=DAILY;INTERVAL=1;BYHOUR=00;BYMINUTE=30'),
How It Works
A database link is required to access the data. This recipe focuses more on the synchronization process, but the creation of the database link is demonstrated here. This link, when used, will remotely log into the HR instance as the HR schema owner.
The procedure sync_hr_data reads all records from the HR instances. It does so in a BULK COLLECT statement, because this is the most efficient method to read large chunks of data, especially over a remote connection. The procedure then loops through each of the employee records updating the local records, but only if the data changed, because there is no need to issue the UPDATE unless something has changed. The NVL is required in the WHERE clause to accommodate values that are NULL and change to a non-NULL value, or vice versa.
The final step is to schedule the nightly job. The CREATE_JOB procedure of the DBMS_SCHEDULER package completes this recipe. The stored procedure sync_hr_data is executed nightly at 12:30 a.m. See Recipe 11-1 for more information on scheduling a nightly batch job.