HOW TO CREATE LINKED SERVER AND HOW TO QUERY ORACLE DATABASE FROM SQL SERVER MANAGEMENT STUDIO.
SQL server 2017 makes it easy to configure linked server.
You can use open-query to get or manipulate data of linked server database.

CONFIGURATION SUMMARY:
PROVIDER : oracle provider for oledb.

DATA SOURCE : It is usually a combination of oracle server IP: port number/t.n.s listener name.
e.g. 192.168.1.10:5121/orcl

OPEN QUERY : select * form openquery(linked_server_name, ' sql query statement ')

Configure a linked server to enable the SQL Server Database Engine to execute commands against OLE DB data sources outside of the instance of SQL Server. Typically linked servers are configured to enable the Database Engine to execute a Transact-SQL statement that includes tables in another instance of SQL Server, or another database product such as Oracle. Many types OLE DB data sources can be configured as linked servers, including Microsoft Access and Excel. Linked servers offer the following advantages:
• The ability to access data from outside of SQL Server.
• The ability to issue distributed queries, updates, commands, and transactions on heterogeneous data sources across the enterprise.
• The ability to address diverse data sources similarly.

An oracle linked server require OLE DB provider.
An OLE DB provider is a DLL that manages and interacts with a specific data source.
If your oracle database is not in same server then you have to install The Oracle Data Access Components (ODAC) for Windows consists of Oracle client side drivers.

An OLE DB data source identifies the specific database that can be accessed through OLE DB. Although data sources queried through linked server definitions are ordinarily databases, OLE DB providers exist for a variety of files and file formats. These include text files, spreadsheet data, and the results of full-text content searches.

Typically, linked servers are used to handle distributed queries. When a client application executes a distributed query through a linked server, SQL Server parses the command and sends requests to OLE DB. The row set request may be in the form of executing a query against the provider or opening a base table from the provider.

свернуть