2

I have two servers (both servers are on Windows OS):

  1. Server_A running Oracle 12c
  2. 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.

0

1 Answer 1

3

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<name>.ora. A typical HS file usually just contains:

HS_FDS_CONNECT_INFO = <name of the ODBC connection name>
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=<name of the ODBC connection>)
      (ORACLE_HOME=<full path for oracleHome>)
      (PROGRAM=dg4odbc)
    )
  )

Debug: Check the listener log

c:> lsnrctl reload

4) Add a TNSEntry

<Name> =
   (DESCRIPTION=
      (ADDRESS=(PROTOCOL=TCP)(HOST=<Host>)(PORT=<port>))
      (CONNECT_DATA= (SID=<ODBC connection name>))
      (HS=OK)
   )

Debug c:\> tnsping <tns entry name>

5) Create the db link

create database link <name> connect to DUMMY identified by " " using '<tns entry name>';

Debug: SQL> select * from dual@<dblinkname>;

pre 10g, we used to be able to simply create database link <name> using '<tns entry>'; 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...

3
  • @SajjaD I don't have experience with update/insert/delete across an HS dblink (I can't think of a reason why it wouldn't work, however...). That said, you can execute procedures across an HS dblink using dbms_hs_passthrough: docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_hspass.htm Apr 11, 2016 at 12:52
  • Depending on the bit-version of dg4odbc.exe (32 or 64) you will need to set up a 32 or 64 bit ODBC connection. You can check the bit version of dg4odbc.exe by running it from a commandline and looking at the output. It is located in %oracle_home%\bin\dg4odbc.exe. There's some interesting comments and settings here related to connecting to Unicode databases: oracle-base.com/articles/misc/….
    – Wouter
    Nov 28, 2016 at 14:06
  • Sidenote: You need to set up a sepparate ODBC link for each SQL Server database you want to connect to. It is not possible to query other databases than the one the ODBC connection uses as default.
    – Wouter
    Nov 28, 2016 at 14:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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