Creating a SQL Server 2014 Linked Server for an Oracle 11g Database

By:   |   Updated: 2016-07-29   |   Comments (2)   |   Related: > SQL Server and Oracle Comparison


Problem

I need step-by-step instructions for installing an Oracle database server and setting up a SQL Server Linked Server that can query the data within the Oracle database server. Please include potential issues and work-arounds with both configuring the Oracle database server after installation and the SQL Server Linked Server. Also, include at least one query that I can run to verify the Linked Server can run a SELECT statement.

Solution

Both Oracle and Microsoft have been enhancing their database technology in ways that obsolete some prior documentation and examples about how to setup a SQL Server Linked Server that enables queries from an Oracle database. The instructions for this tip are for SQL Server 2014 and Oracle Database 11g Release 2. Both releases are relatively recent, but not likely to change materially going forward (because there have been major new versions in both cases). Therefore, the tip about creating a SQL Server Linked Server to query Oracle data will likely be stable going forward - at least for the referenced versions.

Here's an overview of the steps that will be covered:

  1. Download the files for the Oracle database server.
  2. Copy downloaded files and run the setup program.
  3. Configure the Oracle database server for queries of its sample database.
  4. Run code to set up and configure Linked Server with a special focus on the
    • SQL Server Oracle provider
    • SQL Server Linked Server
    • SQL Server linked login
  5. Verify queries from the SQL Server instance work against the sample database in the Oracle database server.

This tip assumes that you have already installed SQL Server 2014. Learn more - click here.

Download the files for the Oracle database server

You can download Oracle 11g Release 2 from this link. You need to download two zip files:

  • win64_11gR2_database_1_of_2.zip
  • win64_11gR2_database_2_of_2.zip

Copy downloaded files and run the setup Oracle program

The two zip files each contain collections of folders, which in turn contain files. Before running the setup program from within the win64_11gR2_database_1_of_2.zip file, you need to copy the contents of each zip file folder from Downloads to one common folder, such as c:\download.

The folders within the win64_11gR2_database_1_of_2.zip file include the directory path of ..\database\stage\Components\. The win64_11gR2_database_2_of_2.zip file also contains the directory path of ..\database\stage\Components\.

Start by copying all folders and files from win64_11gR2_database_1_of_2.zip. Next, copy all folders from within the ..\database\stage\Components path of the win64_11gR2_database_2_of_2.zip file to the ..\Components folder within the c:\download directory. After the files from both zip files from Downloads are in the same directory (c:\download) outside of Downloads, you can invoke setup.exe to wherever you copied the files, such as c:\download.

Next, follow the instructions in this URL about how to respond to the Oracle GUI for installing Oracle 11g. Step 3 is especially important for the sample database because it names the sample Oracle database; this name is referenced by the code to add a Linked Server in SQL Server pointing at the sample Oracle database. All critical steps for the Oracle database server installation are covered in the URL. I did not take screen shots during my installation of the Oracle database server, but all I did was follow the instructions at the URL. As a sidebar, it may be worth noting that uninstalling the Oracle database server is substantially more involved that installing it.

After the installation process completes, reboot your computer. If your installation went like mine, then your Services listing will include lines like those in the following screen shot.

Linked_Server

Configure the Oracle database server for queries

The sample database for Oracle 11g includes a schema owner named hr. However, the account for the schema owner is locked initially. Also, the account is not connected to the database server. Without remedying these issues, there is no way to query the sample database from a SQL Server Linked Server.

The following screen shot shows how to unlock and connect the account for the hr schema owner in the sample database for the Oracle database server.

  • First, launch SQL*Plus, an Oracle command-line utility, as the system dba. The Oracle database server includes SQL*Plus.
  • After entering SQL*Plus, invoke the alter user command to unlock the account for the hr schema owner
    • The name of the account is hr
    • The password for the account is initially hr
  • Run the conn command so the hr account connects to the server. This allows a confirmation that the account can connect to the server.
  • Close SQL*Plus and the command-line session (for example, click X to close the cmd window).
Oracle SQLPlus scripts to alter a user and connect to a database

Run code to set up and configure Linked Server to sample Oracle database

After installing and configuring the Oracle database server, you can create a Linked Server in SQL Server that points at the Oracle sample database and the hr account. It is possible to do this manually with settings that you make from the Object Explorer or with T-SQL code. The issues that you address are the same either way. This tip focuses on how to implement the Linked Server with T-SQL code, but it also shows the results of the code from Object Explorer and the Linked Server properties dialog box.

Your Linked Server in SQL Server connects to an Oracle database through the ORAOLEDB.Oracle provider. This provider is available by default with a SQL Server 2014 installation. You need to initialize the provider so that the Linked Server using the provider runs in process within SQL Server. The following line of T-SQL accomplishes this. 

-- Configure ORAOLEDB.Oracle provider so that runs in process within SQL Server
exec master.dbo.sp_MSset_oledb_prop 'ORAOLEDB.Oracle', N'AllowInProcess', 1

HHere's a screen shot that shows the result of the setting made in the T-SQL code.

SQL Server 2014 Linked Server Providers

The next step is to add a Linked Server with the sp_addlinkedserver system stored procedure. The sample below sets several key parameters:

  • @server is the name for the Linked Server within SQL Server; in this case the name OrclDB was arbitrarily designated (any other name would work equally well)
  • @srvproduct designates the name of the product to which the Linked Server connects; in this case Oracle is appropriate
  • @provider points to the provider that makes a connection to Oracle from SQL Server; you already know that we are using the OraOLEDB.Oracle provider
  • @datasrc is a name to reference the remote data source; in the context of this demonstration, it is the database name specified during the installation of the Oracle database server (step 3 from the Oracle installation guide) 
-- Add a Linked Server
sp_addlinkedserver 
 @server = 'OrclDB'
,@srvproduct = 'Oracle'
,@provider = 'OraOLEDB.Oracle'
,@datasrc = 'ORCL'

TThe preceding code adds a new Linked Server named OrclDB to the Linked Servers collection under Server Objects in Object Explorer. The settings also affect both the General page for the Linked Server properties dialog box. The following two screen shots show the outcomes of these settings.

Object Explorer in SQL Server Management Studio for Linked Servers


SQL Server Linked Server Properties

After adding the Linked Server, you will need to configure it for remote procedure calls so that your queries from within SQL Server will return results obtained from the Oracle database. The following T-SQL code shows how to configure this setting. 

-- Configure the server for remote procedure calls
exec master.dbo.sp_serveroption 
 @server=N'OrclDB'
,@optname=N'rpc out'
,@optvalue=N'true'

TThe outcome of this code can be seen on the Server Options page of the OrclDB Properties dialog window.

SQL Server Linked Server Properties RPC Out

Finally, you need to add and configure a linkedsrvlogin so that the current SQL Server account can connect to and query the sample Oracle database. The following sample code lets any SQL Server account connect to the sample database with the hr account that was previously unlocked and confirmed to be able to connect to the sample database. 

-- Add a local SQL Server login that connects to the Oracle database
sp_addlinkedsrvlogin 
 @rmtsrvname = 'OrclDB'
,@useself = 'false'
,@locallogin = NULL
,@rmtuser = 'hr'
,@rmtpassword ='hr'

TThe outcome of this code can be seen on the Security page of the OrclDB Properties dialog window.

SQL Server Linked Server Security Properties

Verify queries from the SQL Server instance work against the sample database in the Oracle database server

The purpose of this tip is to create a Linked Server in SQL Server that points to an Oracle database that enables the return of Oracle database values in SQL Server. The following query statement requires upper case characters for both the schema and table names. Also, a four-part name is used as an argument for the FROM clause. The first part is the name of the Linked Server. The second part for the database name is blank, but the name is implicitly specified by the reference to the Linked Server. Aside from these special design issues, the query syntax is similar to other SQL Server query statements for extracting the top three rows from a table. 

-- Required caps for schema and table names
SELECT TOP 3 * FROM OrclDB..HR.EMPLOYEES

TThe following screen shot shows the result set from the preceding query statement.

Sample Result Set between SQL Server and Oralce via Linked Server
Next Steps

I encourage you to enhance your understanding of Linked Servers within SQL Server for Oracle databases using this tip as a starting point. For your convenience, a script file with the code in this tip as well as a database backup is available for download. Some specific additional areas that may be worthy of your exploration include the following.

  • Use a Linked Server to insert, update, and delete data in tables on an Oracle table. Examine T-SQL templates for implementing these capabilities by opening in Object Explorer the Tables collection under the default collection of the Catalogs collection for a Linked Server. By right-clicking a table name and choosing Script Table as, you can automatically generate T-SQL code for selecting, inserting, updating, and deleting data in the Oracle database to which the Linked Server points.
  • The example presented in this tip permits any SQL Server user to access the Oracle database using one specifically designated account on the Oracle database server. However, the Security page of a Linked Server enables you to designate a specific list of one or more local accounts to a SQL Server instance that can access the remote database using one or more accounts on the remote Oracle server.
  • Contrast Oracle 12c with prior Oracle database versions. Oracle 12c introduces a new multitenant architecture that permits one or more pluggable databases to reside within one container database. Prior Oracle versions, such as Oracle 11g, include just one Oracle database per Oracle database instance. An introduction to the multitenant architecture is available at this link. Some adjustment(s) to the steps in this tip for Oracle 11g databases will likely be required when working with multiple pluggable databases in Oracle 12c and beyond. The current Microsoft Linked Server focuses on accessing just one database per Oracle database server.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Rick Dobson Rick Dobson is an author and an individual trader. He is also a SQL Server professional with decades of T-SQL experience that includes authoring books, running a national seminar practice, working for businesses on finance and healthcare development projects, and serving as a regular contributor to MSSQLTips.com. He has been growing his Python skills for more than the past half decade -- especially for data visualization and ETL tasks with JSON and CSV files. His most recent professional passions include financial time series data and analyses, AI models, and statistics. He believes the proper application of these skills can help traders and investors to make more profitable decisions.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2016-07-29

Comments For This Article




Thursday, December 13, 2018 - 10:17:28 AM - Rick Dobson Back To Top (78484)

Hi Sibanda,

Sorry to hear you are having troubles with a linked server.  The details of getting it to work are highly dependent on versions and connection interfaces.  This can make getting it work at first tricky.

I have not actively worked with the content in this tip for over year.  As a result, my recollection about details are not fresh.

In any event, I notice that you are trying to query Oracle 10g.  You do not say what version of SQL Server you are using.  My recollection is that the versions of both Oracle and SQL Server impact the viability of any approach to transferring data.  However, the tip is for Oracle 11g.  Additionally, the tip is for SQL Server 2014.

Let me say that writing the data to a txt file from an Oracle server and then importing the txt file into a SQL Server may be a worthy avenue for you to explore.  You may find this tip (https://www.mssqltips.com/sqlservertip/4430/faster-way-to-transfer-data-from-sql-server-2014-to-oracle-11g/) helpful if you decide to pursue this approach.  

I hope this helps.

Rick Dobson

Thursday, December 13, 2018 - 9:21:00 AM - Brilliant Sibanda Back To Top (78483)

 Good day Rick,

I tried everything as you outlined above, however I seem to get the following error when I run a simple select from a table on the linked server. I have tried searching everywhere for a solution to no avail. Was wondering if you could assist.

Ther error.

Msg 7347, Level 16, State 1, Line 6

OLE DB provider 'OraOLEDB.Oracle' for linked server 'ODBMS' returned data that does not match expected data length for column '[OraOLEDB.Oracle].PMG_LOC_CURR'. The (maximum) expected data length is 12, while the returned data length is 6.

Much advice indicated that I use Open Querry. The below Query gave me the error above.

select * from openquery([ODBMS], 'select * from JDAOWNER.PMGDTLEE')

I am trying to query an Oracle 10g server.

Regards














get free sql tips
agree to terms