none
Cannot connect to Oracle Database with Power Query RRS feed

  • Question

  • Note: Unfortunately as a new member to this forum, I am unable to post screenshots and links, which I had originally posted on the Microsoft Community forum, where it was suggested I should post over here.

    Hi,

    I've struggled trying to get data from an Oracle database:

    1. It doesn't appear in the Get Data drop down as show in various screenshots found online:

    [screenshot of Get Data menu with the From Oracle Database option listed]

    2. I looked at the documentation [link to Microsoft Documentation] explaining I should install 32-bit Oracle Data Access Components (ODAC) with Oracle Developer Tools for Visual Studio (12.1.0.2.4). My Excel is also 32-bit and I imagine that so is Power Query (can't seem to find the info. Just to be sure, I also installed the 64-bit.

    3. After doing this, I could not see the drop down. However, I read elsewhere that it could be accessed from the Other Sources > From OBDC:

    [screenshot of Get Data menu with the From OBDC option listed]

    4. I went through my Windows Admin Tools > ODBC Data Source Administrator 32 bit and there I added a new Microsoft ODBC for Oracle Setup. I could not find any reference to Oracle in the Add section of the 64 bit, even though I installed the drivers. However, since this is 32-bit, it shouldn't matter (?)

    5. I'm going through the ODBC menu in Excel above, I select my connection and I get 2 error messages: one from Windows and one from Power Query / Excel:

    [Screenshot of the error message 1: "The Oracle(tm) client and networking components were not found. Thesec components are supplied by Oracle Corportation and are part of the Oracle Version 7.3 (or greater) client software installation. You will be unable to use these drivers until these components have been installed"]

    [Screenshot of the error message 2: " Unable to Connect. We encountered an error while trying to connect. details: 'ODBC: ERROR [IM004] [Microsoft][ODBC Driver Manager] Driver's SQLAllocHandle on SQL_HANDLE_ENV failed'"]

    I've been stuck for a week with this and I really need help here. I have a deadline to meet and can't get my data unless I go through Oracle Developer and copy paste a million times. Please help figure this thing out. Thanks!


    Monday, September 2, 2019 3:57 AM

Answers

All replies

  • Hi,

    Since this issue is more related to Power Query, I'm moving it to a Power Query forum: https://social.technet.microsoft.com/Forums/en-US/home?forum=powerquery

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    Regards,

    Bella Wu


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.

    Click here to learn more. Visit the dedicated forum to shareexplore and talk to experts about Microsoft Office 2019. 

    Monday, September 2, 2019 8:34 AM
  • Thanks.
    Tuesday, September 3, 2019 1:23 AM
  • **Update:** When running Excel as an Administrator, I am only getting the second error message. Not sure if that makes any difference.

    I've also looked at my environment variables--as I've seen elsewhere they may be related:

    C:\oracle\app\benjamin_oracle\product\12.2.0\client_1;

    C:\oracle\app\benjamin_oracle\product\12.2.0\client_1\bin;

    %SystemRoot%\system32;

    %SystemRoot%;

    %SystemRoot%\System32\Wbem;

    %SYSTEMROOT%\System32\WindowsPowerShell\v1.0\;

    %SYSTEMROOT%\System32\OpenSSH\;

    C:\Program Files (x86)\Microsoft SQL Server\150\DTS\Binn\;

    C:\Program Files\dotnet\;

    C:\Program Files\Microsoft SQL Server\130\Tools\Binn\;

    C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn\

    Tuesday, September 3, 2019 4:14 AM
  • Hi there. What version of Excel are you running (full version number and SKU)?

    Ehren

    Tuesday, September 3, 2019 8:03 PM
    Owner
  • Hi, I'm running Microsoft Excel for Office 365 MSO (16.0.11929.20234) 32-bit (monthly channel).
    Wednesday, September 4, 2019 1:07 AM
  • Ok. If you go to File -> Account and look near the top right section that says "Product Information", what does it say under the Office logo? (It should indicate whether you're using Home & Student, ProPlus, etc.)

    Ehren

    Wednesday, September 4, 2019 4:17 PM
    Owner
  • Hi Ehren,

    Excel for Office 365, desktop, is a on my Office 365 Personal subscription. I also have and Education A1 from work, but that one doesn't come with an desktop version of the Office. Hence I am using my personal account.

    Literally, under the logo, it says:

    Subscription Product

    Microsoft Office 365

    Belongs to: myemail@gmail.com

    This product contains:

    [product icons]

    If I click on the [Manage Account] button, it will bring me to a page, then clicking on the "show my subscription" link, it will show: Office 365 Personal.

    Thursday, September 5, 2019 12:52 AM
  • Ok, thanks for the additional info. Please see the Excel team's response in this thread. Summary: not all connectors are available in the Personal SKU.

    Ehren

    Thursday, September 5, 2019 5:40 PM
    Owner
  • Ok, thanks for the additional info. Please see the Excel team's response in this thread. Summary: not all connectors are available in the Personal SKU.

    Ehren

    Is this actually true though? While the additional connectors do not show up in the menu, I have been able to use some of those "Premium" connectors in Standard versions of Excel 2013 and 2016. You just have to start with a blank query and setup the connection manually. I don't have an O365 version to check, but the Oracle.Database connector seems to exist in Power Query for Excel 2013 and 2016 Standard (I don't have an Oracle DB to connect to, but the only errors I get are ones related to the connection not working properly).
    Thursday, September 5, 2019 8:31 PM
  • Thanks all for your support.

    I managed to connect to the Oracle database.

    1. I had to install some pre-requisites, namely the Microsoft Visual C++ Redistributable packages x_64 and x_86.

    2. I followed the steps in this document: https://manjaro.site/how-to-install-oracle-odbc-driver-on-windows-10/

    It worked like a charm and I also can connect to MySql as bonus.

    Monday, September 9, 2019 12:52 AM