CODEWITHSUNDEEP
exceloracle-databaseodbc
neydroydrec
neydroydrec

Reputation: 7323

Access Oracle Database from Excel: Error with ODBC

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).

Upvotes: 0

Views: 2974

Answers (2)

neydroydrec
neydroydrec

Reputation: 7323

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.

Upvotes: 0

S. Schaefer
S. Schaefer

Reputation: 81

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

Upvotes: 0

Related Questions