Excel connect to Oracle – 64bit and 32bit issues

December 29th, 2010
Goto comments Leave a comment

Wow, thanks to

Process Monitor   http://technet.microsoft.com/en-us/sysinternals/bb896645

I was able track down why I couldn’t connect to Oracle from Excel.

I had wanted to try some of the examples Charles Hooper has posted on connecting to and monitoring Oracle, for example

http://hoopercharles.wordpress.com/2010/01/20/excel-scrolling-oracle-performance-charts/

I kept getting the error “Provider not found”
Now what kind of trace info is there for an error like this in Excel? None AFAIK. Time to start guessing.
I’m on windows 7 64 bit. I have the 64bit 11gR2 Oracle installed.  Excel shows up in task manager as “EXCEL.EXE  *32”. My first guess was, “oh, excel must want the 32bit libraries” so I got the 32 bit instant client from Oracle. Unzipped them into a directory and put them first into the path. Still no dice.
Did a lot of google searches and turned up that I needed

oraoledb.dll

but this wasn’t in any of the instant client zips that I downloaded from

http://www.oracle.com/technetwork/topics/winsoft-085727.html.

Turns out it’s in a download halfway down the page:

*Instant Client Package – ODAC: Includes ODP.NET, Oracle Services for MTS, Oracle Providers for ASP.NET, Oracle Provider for OLE DB, and OO4O with Oracle Instant Client

Downloaded this, put all the dlls in the first directory in my path. Still no dice.

I tried “strace”, but it gave no output. Rrrr.

Then I tried process monitor – bingo.

With process monitor, I filtered on processes containing “excel”, ran the connect, got tons of output, but knew it was a problem with libraries. I found “oraoledb10.dll” among the output. It was listed in an old Oracle 10 install directory. Oracle 10 32bit had been initially been installed. The install gave no warnings but bombed out late in the game so I remvoed the 10g and I installed Oracle 11gR2 64bit. (Oracle 11gR2 32bit won’t even begin the install)
So now, I scoured the registry with regedit and found the old oraoledb10.dll here

HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{3F63C36E-51A3-11D2-BB7D-00C04FA30080}\InprocServer32

I changed this to the new location of oraoledb11.dll
and now it works.


Uncategorized

  1. Trackbacks

  2. No trackbacks yet.

  1. Comments

  2. Marcin Przepiorowski
    December 29th, 2010 at 15:36 | #1
    Reply | Quote

    Hi Kyle,

    I thing that 10g installation did all work for you with registering all necessary 32 bit DLL’s on 64 bit Windows. I tried today with 64 bit only and even when I added register entry still have a problem – Excel is reading Oracle DLL but there is other issue with 32 bit OLE DB access.

    If I found a working workaround I will let you know.

    regards,
    Marcin

  3. Kyle Hailey
    December 29th, 2010 at 17:50 | #2
    Reply | Quote

    Hi Marcin,
    Yeah – it’s possible that the 10g install did other necessary work. Would be interested in nailing down all the steps necessary to get Excel 32bit working on windows 64bit.
    Thanks
    – Kyle

  4. Charles Hooper
    December 30th, 2010 at 01:21 | #3
    Reply | Quote

    My blog article was originally developed with the 32 bit version of Excel 2007 running on 32 bit Vista. My laptop is currently running the 32 bit version of Excel 2010 on 64 bit Windows 7. I was able to reproduce my blog article’s results on my laptop without too much trouble (I initially skipped the step of adding a reference to the Microsoft ActiveX Data Objects 2.8 Library). This particular laptop has the 64 bit version of Oracle Database 11.2.0.1 installed and the 32 bit version of the 11.2.0.1 Oracle client (custom install with all Windows interfaces except the one with MTS in its name) – the client was installed after the database software, so it is listed first in the PATH environment variable. Of course, this means that two copies of the TNSNAMES.ORA file must be maintained, one in each Oracle home.

    As Kyle mentioned, if you have the 32 bit version of Excel, you need the 32 bit Oracle client installed (and preferrably listed first in the PATH environment variable).

    Let me know if you need any additional help. By the way, great idea to use Process Monitor.

  5. Marcin Przepiorowski
    December 30th, 2010 at 14:22 | #4
    Reply | Quote

    I found a solution – http://www.oracle.com/technetwork/topics/dotnet/utilsoft-086879.html – ODAC 11.2 Release 3 and Oracle Developer Tools for Visual Studio (11.2.0.2.1) – it is 32 bit instant driver and can be installed on 64 bit Windows 7.

  6. Haley
    July 12th, 2011 at 20:10 | #5
    Reply | Quote

    Hello, I am running 32 bit Excel 2007 and unable to connect to Oracle 32 bit driver. I am on 64 bit Windows 7.

    What can I do to make a connection from Excel to Oracle? I am a bit of novice, so appreciate your help.

  7. Kyle Hailey
    July 12th, 2011 at 23:46 | #6
    Reply | Quote

    What error do you get?
    Install a 64 bit Oracle 11g on your PC and edit the registry and make sure
    HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{some_code}\InprocServer32
    points to the location of oraoledb11.dll in your Oracle install directory.

  8. Haley
    July 13th, 2011 at 04:52 | #7
    Reply | Quote

    Here is my error message –

    “Unable to connect
    SQLState =IM004
    [Microsoft][ODBC Driver Manager] Driver’s SQLAllocHandle On SQL_HANDLE_ENV failed.

    Any suggestions please?

  9. Rwoeke
    August 27th, 2012 at 15:58 | #8
    Reply | Quote

    Yes Yes Yes, Saved my Life! ThANKS

You must be logged in to post a comment.