Integration from oracle to PostgreSQL database

[satish]

Dear erman,

We have a requirement to fetch and insert data into oracle database from PostgreSQL database and back update in PostgreSQL.

We created dblink using dg4odbc gateway but it has many limitations where it cannot participate in distributed transactions.

Is there any alternative for this.
Some say to use golden gate but it is highly costly for this specific requirement alone.

We need ur valuable suggestions.

Thanks,
Satish

[ErmanArslansOracleBlog]

A web service (hosted by Oracle side) based solution comes to my mind..
Are you using PostgreSQL ODBC driver right? What do you get when you try to do that transactions? any errors?

[satish]

Yes erman,we are using PostgreSQL ODBC driver.When our team used a procedure,it says cannot participate in distributed transactions.

Will send a sample code.Who has to develop this service.Is it dba or developer?

Thanks,
Satish

[ErmanArslansOracleBlog]

Well, as you mentioned; this is a known limitation -> Cannot participate in distributed transactions; they support single-site transactions only..

Postgresql has a feature/concept called "Foreign Data Wrapper". Foreign Data Wrapper for Oracle. So you may have a look at that.. For integrating Oracle and PostgreSQL that concept can be used as well.. It is like create a link from postgres to Oracle.. But I didn't test it, you should check its limitations.

As for publishing web services from Oracle database, you have option . But first you should decide with your developer that it can be done aligned with your transactional logic..  I mean you should decide whether it will work in your scenario or not.. You should also decide the type of web service that you should publish. (rest json, rest xml,  soat etc..)

Some examples with ORDS :

https://www.oracle.com/webfolder/technetwork/tutorials/obe/db/ords/r30/Insert_Update_Delete/Insert_Update_Delete.html

[satish]

Thanks as always.

Dear erman,

We will check the feasibility of service with developer.They say all the logic will be called from concurrent program as and when required.

1)Who should develop this service?Developer or dba
2)Is golden gate flexible from oracle to PostgreSQL?

Thank you,
Satish

[ErmanArslansOracleBlog]

Developers will do it.. (DBAs will assist)
Yes , postgresql-oracle data integration can be done with GG.. -> https://blogs.oracle.com/dataintegration/post/how-to-replicate-data-using-goldengate-postgresql

Also, we have a tool called Striim.
Striim can do it as well -> https://www.striim.com/

If you want to get more info about Striim, send me an email ([e-mail]) from your work email. Put all the decision makers in cc, so I can redirect you to our Striim team and they may give you a solution offer.. We are the distributor of Striim.

Not directly related with this issue but! we also have a tool called Gluent for data virtualization.. you may want to look at that product as well ->  https://gluent.com .. We are a partner of Gluent.

[satish]

Thanks erman.Will update the same to management