During the last week, I’ve attempted to install Oracle Data Access Components 10.2.0.2.20 several times, and to describe it as a headache would be an understatement. I think that a number of the problems I experienced may have been caused by my computer’s registry being a bit of a manky state, possible historic installs of things like their development suite, and so on.

Anyway, this post is intended as a note-to-self of the problems I encountered and the eventual solutions; most of which I did find on the web but somehow the final puzzle pieces evaded easy web-search finds. And again, because I have recently tried to stop my normal usage of an Admin level account, this may have contributed further problems – it certainly added hassle factor to the whole process.

So What is ODAC?

Oracle Data Access Components (ODAC) are the tools you need on a computer that is going to act as an Oracle client – I’m not sure if it’s required if you installed the database server on the same machine, but it certainly is required if the server is on a different machine. I believe (but did not actively test) that even the Microsoft Oracle connection software in the .Net framework requires this software to be installed. I was installing it because the tools for integration with Visual Studio 2005 (and 2003) are included, and I wanted to trial development of ADO.Net applications against Oracle rather than SQL Server Express.

The download also includes a version of Oracle Database Extensions for .Net – which perhaps confusingly needs to be installed on the server. Regardless, I don’t believe that the DB extensions are necessary for a connection from Visual Studio 2005 to an Oracle Database (but they are required for creating .Net Stored Procedures).

The Main Problem?

The installation may appear to complete successfully (it tells you it did), but many people seem to have experienced the following error twice during the install: “The procedure entry point GetProcessImageFileNameW could not be located in the dynamic link library PSAPI.DLL”. Click the picture to see a full-size copy.

Despite the fact that this error has occurred, once you’ve OK’d it a couple of times the install does say that it completed successfully; which is incorrect, and you should ignore. We then get the annoying “Do You really want to Exit?” dialog I documented yesterday. But enough about that wind-up.

A web search on GetProcessImageFileNameW will pull up quite a few websites that tell you what the problem is, but I fear that in this particular case the solution is something of a red-herring (see references for an informative Google hit on this search term). Psapi.dll is the ‘Process Status API’, and the basic cause of the problem is that an old version of psapi.dll is being picked up instead of a nice new one.

A quick run down of the two versions of psapi.dll I found on my machine:

• psapi.dll v4.0.1371.1 18kB (18,192 bytes) (The Problem version);
• psapi.dll v5.1.2600.2180 23kB (23,040 bytes).

Now, for safety I renamed all the files that were not in C:\WINNT\system32 (which was the correct later version) to psapi.dl_old, and I recommend that you do the same, but for various reasons that I can not fully ascertain, do not expect this to completely solve the problem! There are some suggestions that the version of Java that the ODAC installer uses decompresses its own local copy of psapi that is the wrong version, but I had difficulty proving this.

After this so-called correct installation, you should see new entries in your Visual Studio environment, such as a ‘Tools | Connect to Oracle Database…’ menu (image below) and in the Create new Project dialog, an Oracle project entry under the C# or VB project types. To see the image to the right at full-size, please click it.

If you can successfully create a new Oracle project and connect to an Oracle DB, your installation was ok… but if you experience the problems I did, you will get the following message when trying to create a new Oracle project: “C:\Program Files\Microsoft Visual Studio 8\VC#\CSharpProjects\OracleProject.oraproj is not installed. Make sure the application for the project type (.oraproj) is installed.”

If you try and select the “Connect to Oracle Database…” menu entry, I found that it hung the system. Bear in mind that you may need to set up a service name before you can connect to a remote database.

One of the frustrations at this point was that I could get Microsoft’s Oracle data provider to connect to my remote Oracle DB! This is done via the Tools | Connect to Database… menu entry then selecting Oracle as the server type – you need to have set up the service name too. So it seems the problems at this stage are with the VS integration, rather than necessarily with the ‘data access components’.

Simple Fix 1

If you do experience the problems noted above, try this fix: Start the Visual Studio command prompt, and type:

gacutil /i
<ODAC Home>\odp.net\bin\2.x\Oracle.DataAccess.dll

where <ODAC Home> is your installation directory. For me it was the unusual ‘G:\oracle\product\10.2.0\client_1’ (no quotes required in the command line).

This installs the Oracle.DataAccess.dll component into the Global Assembly Cache (GAC).

Give it a reboot just in case, and try the two key items noted above. If they work, lovely, you’re done.

Simple Fix 2

During past problematic installs, Visual Studio had disabled the use of the Oracle tools, so even when they had been reinstalled correctly they did not run. In this case, from the Command Prompt available from the Visual Studio program menu, type:

devenv /resetskippkgs

which will start Visual Studio and re-enable the packages (hopefully).

Uninstalling Issues

The notes above are hopefully more than enough to get you up and running with ODAC and the Oracle tools for .Net. However, I had many problems that seemed to be related to prior install attempts that had not been uninstalled completely. A classic symptom of this is an install attempt where it suggests an installation name of ‘ODACHome5’ and a directory of ‘client_5’ (or any number reflecting your past installations).

Should you find yourself in this situation, I hope that the following key points may be useful:

• Run the latest-version uninstall (or ‘Deinstall’ as Oracle calls it) that you have; that will probably be the one that you got with the ODAC download… this is not available from the ODACHome menu, so you will need to use your original download copy;
• Run installs and uninstalls as an Admin user;
• Make sure that uninstalling selects ‘hundreds’ of separate components to uninstall;
• If the uninstall still leaves lots of program files lying around (e.g. inside your <ODAC Home>, try deleting the directory structure). It will possibly fail on ociw.dll being in use, and honestly I only managed to fix this by some very hairy deletions of registry key entries. The problem appears to be that past uninstalls did not fully remove the ‘OracleMTSRecoveryService’ and sometimes also a Microsoft Distributed Transaction service (which right now, I can’t remember the exact name of or dll);
• I had a pre-existing Java implementation on my machine which may have contributed to the problems, I removed that aswell.

Connecting to a remote Oracle database

If your Oracle server is on a different machine, you need to let the client know where the service is, and you do this through setting up a Service Name.

You also need to make sure that there is a listener set up on the server to ‘listen for’ connections to that database; I’ll assume that you’ve set this up with the default sample instance name of ORCL, and the listener should default to this name too. Finally, you will need to know suitable login names for the ORCL demo DB to test it, and potentially have unlocked user accounts like scott or hr to use the sample schemas. I’m not going to cover any more detail on the server side of things today.

Now, on your client machine you need to set up a ‘service name’ that understands that when you say you want to connect to ORCL, you really mean ORCL on [your database server] machine. Here’s a screenshot that shows what the ‘Net Manager’ software might look like after you have set up the service name; though when you create the service name, the program asks you a sequence of questions to create this screen.

Note that you must actively save this configuration before it takes effect for the rest of your environment, and you must run Net Manager (available from the ODACHome new menu entry) as an admin user.

References

The Oracle ODAC Download page

Oracle Developer Tools for .Net Support Forum

The most useful support thread on the ODT forum

GetProcessImageFileNameW Google Hit

Sysinternals Process Explorer utility (useful to trace which dll’s processes are using)