Skip to main content

Posts

Showing posts from December, 2014

Heterogeneous Database Sync

Hi

As a part of ORACLE to PostgreSQL Migration, I come across to implement a trigger on Oracle, which sync it's data to PostgreSQL. I have tried with
a simple table as below, which is hopefully helpful to others.

Find this link to configure the heterogeneous dblink to postgres.

I believe, the below approach works effectively with the Primary Key tables of Oracle Database.
If we don't have primary key in a table, then the UPDATE,DELETE statements going to fire multiple times in Postgres, which leads performance issues.

ORACLE
CREATE TABLE test(t INT PRIMARY KEY); CREATE OR REPLACE TRIGGER testref AFTER INSERT OR UPDATE OR DELETE ON test FOR EACH ROW DECLARE PRAGMA AUTONOMOUS_TRANSACTION; C number; N number; BEGIN c:=DBMS_HS_PASSTHROUGH.OPEN_CURSOR@pglink; IF INSERTING THEN DBMS_HS_PASSTHROUGH.PARSE@pglink(c, 'INSERT INTO test VALUES('||:NEW.t||');'); n:=DBMS_HS_PASSTHROUGH.EXECUTE_NON_QUERY@pglink(c); ELSIF DELETING THEN DBMS_HS_PASSTHROUGH.PARSE@pgl…