InterfaceError: Unable to acquire Oracle environment handle; ORACLE_HOME is correct and SQL*Plus will connect

Question

I'm getting the standard "DLL load failed; module not found" error when trying to import cx_Oracle. I have the proper instant client installed, the paths are all correct... running Dependency Walker tells me I'm missing the following .dll's MSVCR90, GPSVC, IESHIMS.

I'm running the instant client for Oracle 11g and Python 2.7. Anyone have any ideas? Most of the answers I have found entail an incorrect path, but that doesn't seem to be the case... additionally, I can't find any of those .dll's anywhere else on my system.

EDIT: I ended up installing Oracle XE 11g (32 bit); both Python 2.7 and the cx_Oracle are also 32 bit (I should also add that I'm on Windows). cx_Oracle now installs cleanly; however upon connection I receive an error:

InterfaceError: Unable to acquire Oracle environment handle

The ORACLE_HOME path is correct, as is the bin in the PATH folder...

Answer 1

What version of Windows are you running ? Is it 32 or 64 bit ?

Is your Oracle Instant Client 32 or 64 bit ?

Is your Python installation 32 or 64 bit ?

Is your cx_oracle the correct version ? 32 or 64 bit ?

MSVCR90.dll is part of the Microsoft Visual C++ 2008 SP1 Redistributable package.

32 bit version available here , 64 bit version available here.

IESHIMS.dll will be located in C:\Program Files\Internet Explorer\Ieshims.dll (32 bit Windows location or 64 bit Windows Location) or C:\Program Files\Internet Explorer (x86)\Ieshims.dll` (32 bit Windows Location on 64 bit Windows) , if your version of Windows is Vista or newer.

GPSVC.dll should live in C:\Windows\System32.

Dependency Walker reports these last 2 DLLs as missing because there are used by Windows Error Reporting which use IEFrame.DLL and are delay loaded, which means they may never actually be needed.

I found that in order to get cx_oracle to import cleanly, you need to ensure that the versions of its dependencies match. You also need to ensure that the Oracle client installation matches your ORACLE_HOME and your PATH variable contains %ORACLE_HOME%/bin, which is set as an Environment variable or in the registry, and that your tnsnames.ora file lives in the value TNS_ADMIN is set to. As stated in Emmanuel's answer, the default value for an unset TNS_ADMIN setting is %ORACLE_HOME%\network\admin.

I also rarely used the instant client version of the oracle installer unless absolutely necessary because unlike the other versions it doesn't set always ensure are set or maintained Path, ORACLE_HOME or TNS_ADMIN correctly, which lead to tnsnames.ora and OCI.dll not being found. This gets more complicated when you have multiple Python versions or Oracle versions on the same machine.

To explicit set them you can use Environment Variables (either User or System) , which live in the Control Panel under the System Icon, Advanced system settings task, Advanced Tab, Environment button.

Regarding InterfaceError: Unable to acquire Oracle environment handle, this occurs specifically when opposed to not resolving OCI.dll , cx_Oracle doesn't know which OCI.dll to use, normally this is the case because of the PATH variable containing two or more search directories that contain OCI.dll.

Specifically ensuring your PATH only contains one instant of OCI.dll either from the instant client installation or the Oracle 11G XE instllation should fix your problem.

Did you uninstall the instant client before installing Oracle 11G XE ?

Paste the following in a Command Prompt.

echo The current ORACLE_HOME is %ORACLE_HOME%

echo The current TNS_ADMIN is %TNS_ADMIN%

echo The current PATH is %PATH%

To see the current value of these variables.

Further resources

• Example picture of setting environment variable
• cx_oracle mailing post regarding missing TNS_ADMIN
• Installation instructions for cx_oracle on Windows

Answer 2

I had the same issue: you have to set the variable ORACLE_HOME to match your Oracle client folder (on Unix: via a shell, for instance; on Windows: create a new variable if it does not exist in the Environment variables of the Configuration Panel) since this is the way the cx_Oracle module can link to it.

Your folder $ORACLE_HOME/network/admin (%ORACLE_HOME%\network\admin on Windows) is the place where your tnsnames.ora file should exist.