Linked Server that connects to Oracle

Sometimes you need to pull data from an Oracle database into your SQL Server database.  No magic is required, you just need to use a Linked Server.  This article will show you how easy it is.

I am going to make the following assumptions 1.) that you already installed the Oracle Client http://www.oracle.com , it is pretty much a next, next, done installation.  2.) That you know the database name and service name you want to connect to,  3.) You have a username/password for the Oracle database.  All of this is required so you can create a Linked Server.

 

Below is a screenshot of the VoterData table in our Oracle Database.  We want to create a Linked Server so we can query the data directly from SSMS instead of exporting it out.

 

 

Once the Oracle Client is installed (download from Oracle’s site), we need to open Net Manger, see below for the icon.  We are going to configure the Oracle network settings to connect to a database.

 

This brings up the Oracle Net Manager program.

 

Click the + sign on the Service Naming list, and then click the Green + sign on the left to create a new connection.

This starts the connection wizard.  For the Net Service Name, you can name it whatever you want.  I am going to call mine Test, but you can call it anything.

 

The next screen is the protocol that will be used, we are going to choose TCP/IP.

 

You need to know the hostname or IP address of the location of the Oracle database you wish to connect to.  Mine is wincomputer.  You will also need to know the Port Number.  1521 is the default, but some companies use 1523 or some other port.  You will need to know what yours is.

Now you need to provide the service name.  The Oracle DBA can provide this to you or you may already know it.

 

Click Finish when you are done to complete the wizard.

 

When you are done, the screen should look like this, with an entry named TEST user the service naming tree.

 

Now we are going to confirm that the Oracle connection works.  Goto a command prompt and type in:  tnsping test

Tnsping is an Oracle supplied utility that checks to see if you can connect to the listener.

You can read more about it here:  http://www.orafaq.com/wiki/Tnsping

 

Below shows that we have successfully setup our connection and should be able to connect to our database using an alias of TEST.

 

Now in SQL Server Management Studio, drill down on Server Objects and Linked Servers and then Providers.  Then double click OraOLEDBOracle.

 

We need to enable Allow Inprocess.  You can read more about these options here:  https://technet.microsoft.com/en-us/library/ms188095(v=sql.105).aspx

 

Now right click on Linked Servers and choose New Linked Server.

 

Here is how I filled in the first screen.  Linked server can be anything you want, I put TEST.  The provider has been changed to Oracle Provider for OLE DB.  Product name can be anything, but I put Oracle.   Data source is the name that we created in the Oracle Net Manager tool above…Test.  Finally provider string is the configuration file that gets created using the Net Manger tool.  This is the network/admin directory in your Oracle home, and the filename is tnsnames.ora.

 

Here is a screen shot of the location of the Tnsnames.ora file location that you need to provide above.

 

Now we need to goto the Security tab and select: Be Made using the security context.  Then we need to provide the username and password that the link will connect as.  This is an Oracle account.

 

When done, right click the TEST Linked Server that we created and Test Connection.

 

If everything is setup correctly, you should see the box below.

Now in SSMS, drill down on the TEST Linked Server, and you can view the tables you have access to.  I do not know why the table names are showing up twice below.  When I tested it, it showed up correctly with one name.

 

In SSMS, I run the following query, and I am pulling the information from Oracle!!!

The syntax for the FROM section is as follows:

test..TEST.VOTERDATA

test is the Linked Server name that we created.

.  is the database name, but we can leave this blank, hence the ..

TEST.VOTERDATA is the schema name and table name.  I have found these need to be uppercase or else you get an error message that says table does not exist.

 

 

And there you have it.  You can now include Oracle data in your SQL Server queries.  Happy querying!