Oracle to SQL server communication - JDBC vs ODBC

3
votes

Just looking for people's opinion regarding communication between an Oracle 10G database running on Unix and a SQL Server database running on Windows.

I have a requirement to write a batch job that will read data from the Oracle database and populate the data onto a table on the SQL Server.

Oracle supports "Heterogeneous Services" features where i can use an ODBC driver to create a database link from the Oracle database to the SQL Server database.

The other option i have is use a Java process that uses JDBC to connect to the SQL Server and populate the data using the JDBC connection without database links.

Curious to know the advantages and disadvantages of the two.

Thanks

1
sql-serverdatabaseoraclejdbcodbc

1 Answers

5
votes

From a performance standpoint, it's almost certainly more efficient to go with the database link. Otherwise, your Java process is going to have to pull the data over the network from the Oracle database and then write it back over the network to the SQL Server database. The direct connection will only require a single network round-trip.

From a maintenance standpoint, a database link will generally result in substantially less code being written. It's a lot easier to write

INSERT INTO destinationTable@sqlServer( <<column list>> )
  SELECT <<column list>>
    FROM sourceOracleTable

than it is to do the same in Java even if all the JDBC is being done by an ORM layer.