Oracle Database Connectivity for the Microsoft BI stack (Part 1 Oracle Client)
There's lots of blog posts out there on how to get your SQL Server BI environment to talk to Oracle, but lot's of it is a bit incomplete, so I'm going to take a crack an publishing a complete guide. I'll start with installing the Oracle client and getting it running with BIDS (Visual Studio) and 64bit Analysis Services. Also I'll be using 64bit Windows Server 2008 R2 since there’s not much info out there on connecting from the latest versions of Windows.
So first off, acquire the Oracle client software. For most Microsoft BI products, you will want to use the Oracle OleDb Provider. This gives the best performance for SSAS and SSIS. So we'll want an Oracle client package that includes OleDb. The Oracle OleDb provider requires OCI, and so once we have the Oracle OleDb provider and OCI installed, we will be able to use the System.Data.OracleClient .NET provider too, and also the Microsoft Connector for Oracle by Attunity for SSIS.
Oracle Client software ships in 3 different packages:
- The Client Disk included with each Oracle Database for Windows build.
- Oracle Data Access Client (ODAC), 32bit and 64bit
- Oracle Instant Client
You can also find links to these, some useful technical information and entertaining marketing material at the Windows Server Technology Center on the Oracle Technology Network. Anyway, the Oracle Instant Client doesn't include OleDb, so it's out. My preference is to use the ODAC package, especially since it has a XCopy installation package that lets you copy and configure the software manually, instead of running the big-and-sometimes-challenging Oracle Universal Installer. However, for newer versions of the Oracle Client and Database, the ODAC package is not always available.
I'm running Windows Server 2008 R2, which has the same codebase as Windows 7. These platforms aren't officially supported by Oracle yet, so we'll want the absolutely latest and greatest Oracle client software. Don't worry if you're connecting to an older version of Oracle, the latest drivers should work for that too. So we'll be going with the Oracle Client that ships with the latest Oracle Database for Windows. Currently that's the 11.1.07 client for Windows Server 2008 x64, available here: Oracle Database. Since we'll be running a mix of 32bit and 64bit programs connecting to Oracle, we'll need both the 32bit Client and the 64bit Client. On the download page you will see both a Database Server download and a Client download, just get the client downloads. Accept the license agreement, download and Unzip the client package and run setup.exe for each client:
Choose Custom Install:
Now choose where to install the client. I like change the defaults to make it easier to type later. We will install the 32bit client into c:\Oracle\Client32 and the 64bit client into c:\Oracle\Client64. When you install the 64bit client, change “Client32” to “Client64” in the Name and the Path:
On the next screen the installer will check for Prerequisites. Since this package isn’t officially supported on Windows 7 and Server 2008 R2, yet, we need to manually override the OS version checks:
On the next screen choose the client components to install. We will just choose “SQL*Plus” and the “Oracle Provider for OLE DB”.
Then Install, and wait for the Wizard to complete. Then do the same for the 64bit client. After both clients are installed you can test them with SQL*Plus. Open two command prompts, navigate in one to c:\Oracle\Client32\bin and in the other to c:\Oracle\Client64\bin and run SQL*Plus in each one, connecting to your Oracle server using the EZ_CONNECT naming. You should be able to connect to Oracle from both the 64bit SQL*Plus and the 32bit SQL*Plus. Here’s a screenshot:
Notice how we have both a 64bit sqlplus.exe and a 32bit sqlplus.exe running, and both are connected to Oracle. That worked because each copy of sqlplus.exe found oci.dll in the same folder as the sqlplus.exe. In Windows there is a search sequence for dll’s and the application’s “bin” folder (where the .exe is located) comes first in the Dynamic-Link Library Search Order. This won’t help us when connecting to Oracle using other programs.
The OleDb provider is a COM component, so programs don’t load it the same way. COM components are loaded by looking up the name of the component in the registry. The registry entry for the name points to the CLSID Guid, and the registry entry for the CLSID points to the dll. Simple, huh? Gotta love COM, everyone’s favorite component technology from the last century. But this actually turns out to be really cool, since 32bit and 64bit programs on Windows see different registries. You can read all about this registry-redirection magic here: Windows on Windows64 (WOW) Registry Redirection. But the upshot is that programs always find and load the correct Oracle OleDb provider without us having to do anything. But once the Oracle OleDb provider is loaded it still needs to load oci.dll, and now we need to make sure that succeeds. This creates a bit of a dilemma as 32bit processes cannot load 64bid dll’s, and 32bit processes cannot load 64bit dll’s. So we need to influence the DLL search order to ensure that our programs load the oci.dll of the correct “bitness”. The easiest way to do this is by manipulating the PATH environment variable. The Oracle Client installer adds the Client bin folder to the system path, so we now have two entries in the path. We want to take out the reference in the system path to the 32bit Oracle Client, leaving only the 64bit Oracle client. This is because we will be running 64bit Windows Services connecting to Oracle, and we will only be using the 32bit Oracle client in programs running on the desktop. For interactive desktop programs we can simply change the PATH before we launch them. Windows Services always see the PATH as it was on boot up.
So to finish our configuration of the Oracle clients we just have two more tasks left. Remove the 32bit Client bin folder from the system path:
And Reboot, so that Windows Services can see the PATH change. After the reboot, we’ll connect to Oracle in BIDS. To do this we need to set the PATH first. Enter the following two lines in a batch file called vs.bat:
set path=c:\oracle\client32\bin;%path%
"C:\Program Files (x86)\Microsoft Visual Studio 9.0\Common7\IDE\devenv.exe"
Older Oracle Clients had a bug that would have required us to use the 8.3 name of the Program Files (x86) folder, but that’s fixed in the version we are using. If you are using an older Oracle client, your vs.bat should look like:
set path=c:\oracle\client32\bin;%path%
"C:\Progra~2\Microsoft Visual Studio 9.0\Common7\IDE\devenv.exe"
Now launch BIDS, create a new Analysis Services project, and create and test a new Data Source using the Oracle OleDb provider:
That’s it. You’ve got the 64bit and 32bit Oracle providers working. In the next post in this series we’ll create and process a simple Analysis Services cube based on this Data Source.
David
dbrowne_at_microsoft
Comments
Anonymous
November 12, 2010
Thanks. This article is very helpfulAnonymous
October 27, 2014
After about 2 solid days of trying to get my new 2012 server up and running building off of Oracle this article finally got me there. Thanks!Anonymous
November 20, 2014
The comment has been removed