Oracle Transparent Gateways versus Generic Connectivity

I am setting up an interface from an Oracle 9.2 database to a Microsoft SQL Server database.

Each hour I need to update data in the Oracle database using data selected from a few tables in the MSQL Server database.

The number of records being selected should be fairly insignificant, e.g. thousands of records at the most, and the size of the records are small - character based data.

Before I prototype I am curious to hear others' experiences with the two types of Heterogeneous Services Agents: 1) Oracle Transparent Gateways
2) Generic Connectivity

I am leaning towards Generic Connectivity for the following reasosns:

• it is intended for low-end data integration solutions
• doesn't require extra licensing costs on top of my database license

I'd like to hear good reasons why I shouldn't choose Generic Connectivity.

I read that a feature of Transparent Gateway over Generic Connectivity is full support for two phase commit. That is not applicable to my situation as I am only selecting data from the MSQL Server database. I am not updating data in the MSQL Server database. I am not doing distributed updates.

The difference regarding supported Oracle SQL Statements and Functions should also be not applicable as I only need to SELECT data from the MSQL Server database.

I think the only deciding factor would then be performance/reliability related.

I read that the Transparent Gateway provides better performance (http://www.oracle.com/gateways/gateway_bundles/faq.html) but how much more robust/reliable is the Oracle Transparent Gateway than Generic Connectivity? Any experiences regarding this? Received on Wed Feb 16 2005 - 17:58:30 CST