Sync data between two Oracle db tables

Question

I have to sync data from one Oracle db1 table tb1 to another db2's table tbl2. To keep simple, I can truncate data on db2 tbl2 and then copy all the data from db1 tbl1. Both tables have the same data column structure.

Since two tables are on different databases, I am thinking to use a media file like txt or xml as a media. I export data from db1 tbl1 to the media first, and then insert data from the media like inserting data from a bulk of text.

This simple project will be a console app in .Net(I am using VS 2008). I prefer to make this app generic so that only configuration settings are: two connection strings, and two table names. I'll use text file or xml as media to avoid to provide detail information about data types for each column. I am not sure if there is API or classes available to export data and import data for Oracle db?

Answer 1

Why reinvent the wheel? Oracle has bi-directional replication.

Answer 2

Forget .Net. Use DataPump or old imp/exp.

The problem with XML/text formatting is what do you do with complex datatypes (like, for example, XML). You'd spend a massive amount of time developing and testing a generic solution that correctly escapes data, caters for BLOBs and other exotic datatypes...