0

I'm trying to import data from Oracle to Excel. I was hoping to find Oracle DB on the drop down under Get Data as see online:

enter image description here

That picture not being there I hit the documentation explaining I should install 32-bit Oracle Data Access Components (ODAC) with Oracle Developer Tools for Visual Studio (12.1.0.2.4).

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:

enter image description here

I went through my Windows Admin Tools > ODBC Data Source Administrator and there I added a new Microsoft ODBC for Oracle Setup.

My database now appeared in the ODBC database choice drop down. I call for the query and... I get two errors:

First a Windows error:

"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"

Then power query error:

"ODBC: ERROR [IM004] [Microsoft][ODBC Driver Manager] Driver's SQLAllocHandle on SQL_HANDLE_ENV failed"

So now I'm a litte confused and don't understand what this means and what I've done wrong. Other posts here didn't point to the right direction (I think).

neydroydrec
  • 6,973
  • 9
  • 57
  • 89
  • Duplicate of https://stackoverflow.com/questions/13626024/oracle-odbc-drivers-sqlallochandle-on-sql-handle-env-failed ? – Olivier Depriester Aug 29 '19 at 07:12
  • Is your Excel also 32-bit? You can just install the **Oracle Data Access Components (ODAC)** - Oracle Developer Tools for Visual Studio are not required. – Wernfried Domscheit Aug 29 '19 at 07:17
  • Which ODBC Data Source Administrator did you open? Be aware there is an 32-bit version (`c:\Windows\SysWOW64\odbcad32.exe`) and a 64-bit version (`c:\Windows\odbcad32.exe`). By default you will start the 64-bit version which might be wrong. – Wernfried Domscheit Aug 29 '19 at 07:20
  • Thanks guys. Excel 32 bit and used ODBC 32 bits as well. I installed ODAC and ticked off the Visual Studio bit. – neydroydrec Aug 29 '19 at 07:42

2 Answers2

0

You should add the Oracle Home (the path where the 32-bit Oracle Data Access Components is installed) to your PATH systemvariable. You need an entry for the oracle_home and oracle_home\bin, i.e. your installation path is c:\oracle\client12 then add PATH=c:\oracle\client12;c:\oracle\client12\bin;c:\windows\system32.... to the path variable. In most cases the pc needs a reboot after the path was changed.

You add an ODBC-DSN with the driver "Microsoft ODBC for Oracle". This odbc driver is deprecated. You should use the native oracle driver named "Oracle in ...." in the odbc dialog. enter image description here

The used Oracle Home needs a tnsnames.ora file (or other tns resolve method like onames, ip) to resolve the tnsalias to a database service or sid. The file is located in your Oracle Home path in the subdirectory network\admin. Example tnsnames.ora: 

orcl.example.com =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = myserver.example.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )

The tnsalias orcl.example.com is resolved to myserver.example.com with listner port 1521. The connected database service is orcl.

This tnsalias is used for the definition of the ODBC DataSourceName: enter image description here

If the drop down box has no values you can overwrite the box with your defined tnsalias. In the example orcl.example.com

S. Schaefer
  • 81
  • 1
  • 5
  • Thanks! I've tried using these, however there's no TNS Service Name listed. I've then found this post https://stackoverflow.com/questions/11437522/odbc-administrator-cant-find-oracle-tns-names-file but I'm not sure of the answer. How can it connect to a database without having a host url or ip provided? – neydroydrec Sep 04 '19 at 01:04
  • This was close to being the answer. I had some missing pieces and the ora file pointing to the TNS wasn't were it was supposed to be so I couldn't get it right. – neydroydrec Sep 09 '19 at 00:54
0

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.

neydroydrec
  • 6,973
  • 9
  • 57
  • 89