Sql-server – Oracle equivalent for Linked Server and can you join with SQL Server

linked-serveroracleoracle-12csql serversql-server-2008-r2

In SQL Server I can create a view that is a join between two tables that are in completely different servers using Linked Server. If I change one of the servers from SQL Server to be Oracle, can I still do the same?

I need the join table to be in Oracle

Best Answer

Yes, you can. In Oracle, this is called "Heterogeneous Connectivity" This Oracle admin manual has details. The basic outline is

  1. Install ODBC drivers on Oracle server
  2. Configure ODBC to talk to the SQL Server DB (create a system DSN)
  3. Test the ODBC
  4. Configure Global_Names parameter to false.
  5. Create initodbc.ora file to configure Heterogeneous services
  6. Modify listener.ora to connect to both Oracle and ODBC drivers
  7. Modify tnsNames.ora file
  8. Reload the listener to see the above changes.
  9. Create a DB Link from Oracle to the ODBC connection.
  10. Test via SQL Select statement.

This article walks through an example case of doing the above steps with details.

Related Question