2

Is there a way for Oracle to read from SQL Server?

We are basically trying to read from a SQL Server table and update the information in Oracle. We used to have a database link in Oracle to another Oracle database.

Now the application is replaced by a new application that runs SQL Server, so we're trying to replicate the same process but now it's Oracle to SQL Server. Hope that makes sense.

  • 3
    You can still create a database link in Oracle that points to server. That requires using Generic Connectivity and the Transparent Gateway (the Transparent Gateway for ODBC is free and reasonably easy if your Oracle database is running on Windows). If you're trying to do real-time replication, that's going to be a challenge because all the replication technologies that are built on the database link won't work for heterogenous connections. – Justin Cave Apr 7 '16 at 18:44
2

The Oracle database server accepts SQL statements that query data stored in several different databases. The Oracle database server with the Heterogeneous Services component processes the SQL statement and passes the appropriate SQL directly to other Oracle databases and through gateways to non-Oracle databases. The Oracle database server then combines the results and returns them to the client.

Database Heterogeneous Connectivity Administrator's Guide

Oracle provides heterogeneous services to query non-Oracle databases like SQL Server. If you need some guidance here are some useful links.

  • Connecting to Non-Oracle Data Sources Through Gateways

  • How to Set up Third Party Databases Drivers in Oracle SQL Developer

  • Heterogeneous Services - Generic Connectivity

    0

    We use a product called Easysoft. It's not free but allows you to run queries on Oracle as if the database/tables was local.

    Alternatively you can use replication to copy data from SQL Server to Oracle (or vice versa, edition. Oracle -> SQL Server requires Enterprise I believe).

    Not the answer you're looking for? Browse other questions tagged or ask your own question.