I am currently working with SQL Servers and Oracle servers and products communicate with both databases, In my previous job where we used only SQL Server everything was easy as it was native to Microsoft’s products, however the world of .Net with Oracle has its pain points. A particular scenario i came across today was having a web server with Oracle 9i Data Access Components and 11g Data Access Components on the same server and getting them to work for two different ASP.Net sites. I initially installed the ODAC components without the client thinking it was enough for the ASP.Net site to work, however as it turns out there is more to it than i thought it would be
When i started Oracle 9i client components where already installed on the server and the Products using 9i components where working fine. However the moment I installed the 11g Data Access Components the Products using 9i stopped working throwing exceptions and neither where products using 11g components working. So i had to uninstall 11g Data Access components and leave Product A working. So sort this problem i googled’ a lot and found information scattered all over the place, In my need to have everything in one place i thought i jot down a few notes. So to fix the issue I had to do the following
Install a Oracle 11i client home.
Install Oracle 11i Data Access components into the home created in the previous step
The we have to make sure the odp.net folder which resides under the home directory has permissions for ASP.Net and Network Service accounts for read and execute on all sub folders and files.
It doesn’t end there if your connection strings use TNS names configure tnsnames.ora in the network\admin folder of the 11i client folder. In our case the 11i Components where used to access 9i databases so i just copied the 9i tnsnames.ora file into the 11i client.
Once all this is done you should be good to go with your applications. Most provider related exceptions should be resolved when you follow this procedure.