Connecting Oracle using ODP.NET on Windows x64

Recently I’ve worked very hard to find correct package and working solution to connect Oracle database using ODP.NET on Windows x64. I wish others won’t get the same pain as I had so documenting what I’ve done for the same.

If you are doing fresh installation then you may skip this cleaning steps otherwise you should consider cleaning up Oracle completely from your workstation. To do so,

  • Use Oracle uninstaller to remove Oracle from your machine.
  • In case you don’t have uninstaller remove Oracle directory manually from your machine including registry keys, Path environment variable , Oracle services from registry . You can get plenty of post on Internet for the same. Once done restart your machine.
  • Next to find out number of Oracle ODP.net dlls,policy file still registered in GAC , To do so try following script  ,

gacutil /l | find /i “Oracle” > c:\Oracle_versions\oracle.txt

             Once executed you will get list of dlls,policy files registered in your machine.

  • It’s time to remove all old GAC entries , To do so try something like ( Replace with actual dll and policy file name )

gacutil /u Oracle.DataAccess
gacutil /u Policy.9.2.Oracle.DataAccess
gacutil /u Policy.10.1.Oracle.DataAccess
gacutil /u Policy.10.2.Oracle.DataAccess

OR,

gacutil /u “Oracle.Management.Omo, Version=2.111.7.20, Culture=neutral, PublicKeyToken=89b483f429c47342, processorArchitecture=x86”
gacutil /u “Oracle.VsDevTools, Version=2.111.7.20, Culture=neutral, PublicKeyToken=89b483f429c47342, processorArchitecture=x86”
gacutil /u “Oracle.Web, Version=2.111.7.20, Culture=neutral, PublicKeyToken=89b483f429c47342, processorArchitecture=x86”
gacutil /u “Policy.2.102.Oracle.DataAccess, Version=2.111.7.20, Culture=neutral, PublicKeyToken=89b483f429c47342, processorArchitecture=x86”
  • Once done , restart your machine.

Now it’s time to install Oracle client and ODP.NET . Here are recent compatible version with Windows x64

To get Oracle ODP.NET and .NET Application running on Windows x64 machine for x86 application

  • Install Oracle client “Oracle Database 11g Release 2 Client (11.2.0.1.0) for Microsoft Windows (x64) “ from here ,

http://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-win64soft-094461.html

Select “Run Time” option while installing

  •  Install ODAC 32 bit driver “ODAC 11.2 Release 3 and Oracle Developer Tools for Visual Studio (11.2.0.2.1)” from here ,

http://www.oracle.com/technetwork/topics/dotnet/utilsoft-086879.html 

( While installing Windows will complaint “ You are installing unsupported version of software” , don’t listen it and just go with installation ) Compile .NET application with “Any CPU” or “x86” processor type and application should able to connect Oracle. ( I assume before installing above prior versions of Oracle dlls from GAC, policy files , registry cleanup , and machine.config is done ) To get Oracle ODP.NET and .NET Application running on Windows x64 machine for x64 application ( In case of SharePoint deployment )

  •  Install Oracle client “Oracle Database 11g Release 2 Client (11.2.0.1.0) for Microsoft Windows (x64) “ from here ,

http://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-win64soft-094461.html 

Select “Run Time” option while installing

  • Install ODAC 64 bit driver “64-bit ODAC 11.2 Release 3 (11.2.0.2.1) for Windows x64 “ from here ,

http://www.oracle.com/technetwork/database/windows/downloads/index-090165.html

( Select the same Oracle home as of above client.It’s command based installation so make sure you use file path correctly ).On my machine this installation is like , Client_1 is for Oracle client and Client_2 for ODAC)

Advertisements

Connecting SharePoint list to Excel Workbook

Recently, I was having a requirement to connect Excel workbook with SharePoint list. Here is very simple solution to synchronize SharePoint list with excel. Please note it’s one way synchronization, i.e: if changes done in excel sheet it will not be reflected back to SharePoint list, but changes done in list will be synchronized to excel.

Step 1: Open SharePoint list and use Export to Excel icon to export the view into Excel. Let’s save it as CustomerView.iqy

Step 2: Create new excel sheet, Open Data tab and click Connections.

Step 3: Use Add – >Browse For More.. button to find CustomerView.iqy file which was saved in step 1.


Step 4: Modify the connection string , copy it in the Notepad and modify with actual details.

There are four parts in this xml chunk ,

  • View ID – GUID of the view
  • List ID – GUID of the list
  • Web Path – Path which points to _vti_bin
  • Root Folder Path – Path of the list

Step 5: Once modified, Click OK and you get the following warning . Click Yes.

Step 6: Go to Data tab and click Existing Connections option ,

Now , select the connection and open it.

Step 7: Keep the table option selected , use the range selector to select cell range.

Once done, click OK and here you are,

Step 8: To test the  synchronization , Added two new document in the SharePoint list

Use Refresh All button in the Data tab to get the updated details,

Here it goes,