Connecting Oracle Database and SQL Server – Basic Concepts

It is required sometime to connect oracle database to SQL Server database using uni-direction or bi-direction methods. Since these are two different database so something as simple as direct DBLINK will not work here. Below are the basic concepts of making connection from SQL SERVER to Oracle and vice-versa.

1.CONNECTING FROM SQL SERVER TO ORACLE USING LINKED SERVERS

PURPOSE/FUNCTION:

Use this solution to Read data of Oracle database table from MSQL Server Database.

If you need to retrieve and use Oracle data in your SQL Server applications, then SQL Server’s ‘linked servers concept’ can be used. SQL Server’s linked servers functionality lets you seamlessly integrate your SQL Server applications with tables, indexes, and views stored in Oracle.

PRE-REQS

Need to install the Oracle Data Access Components (ODAC) on the SQL Server 2008 system

The Oracle Data Access Components (ODAC) software provides the network libraries that are required to establish connectivity to the Oracle database.

HIGH LEVEL STEPS

Install Oracle Data Access Components (ODAC) software
Configure connection to Oracle server by modifying Oracle’s tnsnames.ora file
Configure the Linked Server by using either SQL Server Management Studio (SSMS) or by executing T-SQL statements
Execute Queries with proper syntax

Queries that are directed to linked servers use the following four-part naming syntax: linked_server_name.catalog(database)_name.schema_name.table_name. For example, to select all of the rows from the EMP table you would use the following SQL statement:

SELECT * FROM \[OR-PORT-TESTORA11G\]..SCOTT.EMP.

OR-PORT-TESTORA11g is the linked server name. When using the Oracle OLE DB provider, the catalog name should be left blank. The schema name is SCOTT. This corresponds to what SQL Server users would consider the database name. The table name is EMP.

REFERENCE

Microsoft Article: How to get up and running with Oracle and Linked Servers

https://blogs.msdn.microsoft.com/psssql/2015/07/31/how-to-get-up-and-running-with-oracle-and-linked-servers/

2. CONNECTING FROM ORACLE TO SQL SERVER WITH DATABASE GATEWAY FOR SQL SERVER (DG4MSQL)

PURPOSE/FUNCTION

Use this solution to Read data of Microsoft SQL server table from Oracle database.

Note that unlike setting up a connection to Oracle Database from SQL Server which is fairly easy, this solution is not so straight forward and requires installing another Oracle software component which acts as middleman.

Starting with Oracle database 11g, Oracle now provides a Database Gateway for MS SQL Server and various other databases.

PRE-REQS

Need to install and configure the Oracle Database Gateway for Microsoft SQL Server (DG4MSQL) software on the Oracle side

HIGH LEVEL STEPS

Download and Install Oracle Database Gateway for Microsoft SQL Server
Configure Database Gateway for Microsoft SQL Server (DG4MSQL)

— Configuring requires modifying configuration files with MSQL server details, starting anew listener process on the Oracle side and creating a database link.

Execute Queries using DBLINK

Example:

SQL> select count(1) from “SQLView”@dblink_to_sqlserver;

Here SQLVIEW is the table on the MSQL server side and dblink_to_sqlserver is the dblink created in Oracle server pointing towards MSQL Server database.

Reference:

Master Note for Oracle Gateway Products (Doc ID 1083703.1)
How to Setup DG4MSQL (Database Gateway for MS SQL Server) on Windows 32bit (Doc ID 466267.1)
Oracle Database Gateway Configuration Utility (Doc ID 1351616.1)
How to Configure DG4MSQL (Oracle Database Gateway for MS SQL Server) Release 11.1 or 11.2 on Linux x86 32bit – Post Install (Doc ID 437374.1)
How to Configure DG4MSQL (Oracle Database Gateway for MS SQL Server) 64bit Unix OS (Linux, Solaris, AIX,HP-UX) post install (Doc ID 562509.1)
Database Gateway and Generic Connectivity (DG4ODBC) Licensing Considerations (Doc ID 232482.1)
Installation Overview For Oracle Database Gateway Products (Doc ID 1351618.1)
Database Gateway information page: http://www.oracle.com/technetwork/database/gateways/index.html

IMPORTANT NOTE: There is another way to connect to MSQL Server from Oracle which is called “Connecting Oracle to SQL Server with Database Gateway for ODBC (DG4ODBC)” but that solution has some limitations as compared to “Connecting Oracle to SQL Server with Database Gateway for SQL Server (DG4MSQL)”.

Please read below Oracle Note Id to understand those limitations:

Functional Differences between Database Gateway for ODBC (DG4ODBC) and Specific Database Gateways (DG4MSQL, DG4Sybase, DG4Informix, DG4Teradata) (Doc ID 252364.1)

Author
Recent Posts

Brijesh Gogia

I’m an experienced Cloud/Oracle Applications/DBA Architect with more than 15 years of full-time DBA/Architect experience. I have gained wide knowledge on Oracle and Non-Oracle software stack running on-prem and on Cloud and have worked on several big projects for multi-national companies. I enjoy working with leading-edge technology and have a passion for Cloud architecture, automation, database performance, and stability. Thankfully my work allows me time for researching new technologies (and to write about them).

Connecting Oracle Database and SQL Server – Basic Concepts

Related posts: