Oracle Linked Database Server

Answers

You can create an Oracle linked server on your SQL Server instance to query data on Oracle from SQL Server. Below are the steps you need to do:

Install the Oracle 64-bit client software. You only need the barest install of the client.
Once you have an installed 64-bit Oracle Home you can proceed with installing the ODAC drivers for a 64-bit environment. The OraOLEDB.Oracle driver installed in this step is what you need to set up your linked server. You'll find the setup.exe for this installation located in the unzipped Install folder.
Open SQL Server Configuration Manager and restart SQL Server services.
Configure OraOLEDB.Oracle provider. In SSMS (SQL Server Management Studio) Object Explorer, navigate down the tree of the SQL instance we've been working with until you're able to view the list of Providers under Linked Servers. Select OraOLEDB.Oracle and right-click.

The Provider Options dialog box appears. Enable the "Allow inprocess" option and close the dialog.

Create Linked Server and Add Logins.

--Create the Oracle Linked Server:

EXEC sp_addlinkedserver @server, @srvproduct, @provider, @datasrc

--For example: If I want to create a linked server called LK_TIPS to the ORATIPS --database on the SRVORA1 server, listening on the default port of 1521 my query --would look like this:

EXEC sp_addlinkedserver 'LK_TIPS', 'Oracle', 'OraOLEDB.Oracle', 'SRVORA1:1521/ORATIPS'

Create a login for the LK_TIPS linked server we just created with a login of 'sa' and a password set to 'password' would look like this:

EXEC sp_addlinkedsrvlogin 'LK_TIPS', 'FALSE', 'sa', 'password'

Alberto Morillo

Posted 2019-09-12T07:36:29.657

Reputation: 116

Please explain further what link means to you? move data from a DB to other DB? how to migrate data? – GiannakopoulosJ – 2019-09-12T07:45:38.437

@GiannakopoulosJ I am trying inserting data to Oracle database through MS SQL – Lawrence Agulto – 2019-09-12T07:46:41.557

@GiannakopoulosJ or inserting data in my MS SQL Database and moving the data inserted to the Oracle database – Lawrence Agulto – 2019-09-12T07:47:45.997

There is a tool SQL Server Migration Assistant that allows you to migrate data from Oracle->MSSQL but you want vice versa. It seems that you have to create a mechanism of extract/load customized for your need. – GiannakopoulosJ – 2019-09-12T11:07:50.177

@GiannakopoulosJ I am trying to query insert data from MS SQL database to Oracle is that possible? – Lawrence Agulto – 2019-09-13T00:07:03.850

Through export/import (in csv?), or ETL tools. I don't know any technique that makes a DB link between heterogeneous RDBMS. – GiannakopoulosJ – 2019-09-13T10:00:54.123

You mean like this: Setting up SQL Server 2014 and Oracle 12c Linked Servers?

– Ronaldo – 2019-09-13T19:59:48.220

I mean like this https://www.youtube.com/watch?v=dpH-C4sS5xw @Ronaldo

– Lawrence Agulto – 2019-09-17T00:05:27.643