Geek asks and answers

Oracle SQL Server Connectivity - JDBC vs ODBC

Just looking for people's opinions on the relationship between the Oracle 10G database running on Unix and the SQL Server database running on Windows.

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

Oracle supports the Heterogenic Services functions, where I can use the ODBC driver to create a database link from an Oracle database to a SQL Server database.

Another option I have is to use a Java process that uses JDBC to connect to SQL Server and populate data using a JDBC connection without any database references.

Curious to know the advantages and disadvantages of these two.

thank

+3
source share
1 answer

In terms of performance, it is almost certainly more efficient to use a database link. Otherwise, your Java process will need to pull the data over the network from the Oracle database, and then write it back over the network to the SQL Server database. A direct connection will only require a single trip through the network.

From a maintenance point of view, a database link tends to result in significantly less coding. It is much easier to write.

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

than doing the same thing in Java, even if all JDBC is done using the ORM level.

+5
source

More articles:


All Articles