Step by step establish a connection between SQL Server 2014 and Oracle 12c I have two servers (both servers are on Windows OS):


Server_A running Oracle 12c
Server_B running SQL Server 2014


I want these servers to be able to see each other, so I can SELECT anything I want in Server_A from Server_B and vice versa.

I only want the ability to run SELECT queries (not INSERT, UPDATE, DELETE or any DDL queries), and also if possible to EXECUTE procedures.

What options or technologies we have for achieving this issue and which one is better?  

Tell me if any software I have to install or any configuration have to be set on each of servers.
I appreciate explaining the instructions step by step.
Partial answer (select SQL Server tables from Oracle):

On the Oracle side, you can create a database link to view data in SQL Server.  Since this is a 5 step process, I've also included some debug steps that I use when diagnosing db links from SQL Server in order to track down where the failure is.

On the Oracle machine:

1) Set up an ODBC connection to SQL Server

start => control panel => Administrative tools => Data Sources (ODBC) => System DSN => Add => SQL Server)

Debug: Press the Test Connection button from the ODBC connection window

2) Create an HS file that points to the ODBC connection

In %oracle_home%\hs\admin, create a new file called init&lt;name&gt;.ora.  A typical HS file usually just contains:

HS_FDS_CONNECT_INFO = &lt;name of the ODBC connection name&gt;
HS_FDS_TRACE_LEVEL = OFF


Debug: change HS_FDS_TRACE_LEVEL = ON, then check for the appropriate trace files in %oracle_home%\hs\trace

3) Add an entry to your listener.ora

If there is already a SID_DESC present in SID_LIST_LISTENER you can add a new one just below it.

SID_LIST_LISTENER =
  (SID_LIST=
    (SID_DESC=
      (SID_NAME=...)
      (ORACLE_HOME=...)
      (PROGRAM=...)
    )
    (SID_DESC=
      (SID_NAME=&lt;name of the ODBC connection&gt;)
      (ORACLE_HOME=&lt;full path for oracleHome&gt;)
      (PROGRAM=dg4odbc)
    )
  )


Debug: Check the listener log

c:> lsnrctl reload 

4) Add a TNSEntry

&lt;Name&gt; =
   (DESCRIPTION=
      (ADDRESS=(PROTOCOL=TCP)(HOST=&lt;Host&gt;)(PORT=&lt;port&gt;))
      (CONNECT_DATA= (SID=&lt;ODBC connection name&gt;))
      (HS=OK)
   )


Debug c:\&gt; tnsping &lt;tns entry name&gt;

5) Create the db link

create database link &lt;name&gt; connect to DUMMY identified by " " using '&lt;tns entry name&gt;';


Debug: SQL&gt; select * from dual@&lt;dblinkname&gt;;

pre 10g, we used to be able to simply create database link &lt;name&gt; using '&lt;tns entry&gt;'; but the connect as identified by section became required starting in 11g even though whatever you enter isn't used (since it uses the login credentials for SQL Server in the ODBC connection).

It is a little old, but I found the following tutorial very helpful when I was learning this process: Installing and configuring Oracle Heterogeneous Services for SQLServer.

Once you have the database link working, on the Oracle side you could then create views/synonyms/materialized views (complete refresh only) over the SQL Server tables for easier access.

As for the other way around (how to view data in Oracle from SQL Server), i'm not sure since i'm exclusively an Oracle DBA...