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

Solution 1

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.

Solution 2

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

Solution 3

UPDATE 06.10.2020

It seems that MacOS DYLD_LIBRARY_PATH unset for security reasons:

Spawning children processes of processes restricted by System Integrity Protection, such as by launching a helper process in a bundle with NSTask or calling the exec(2) command, resets the Mach special ports of that child process. Any dynamic linker (dyld) environment variables, such as DYLD_LIBRARY_PATH, are purged when launching protected processes.

Several possible solutions:

• replace shebang line in scripts from env (e.g. #!/usr/bin/env python2.7) to python (#!.../venvs/project11/bin/python2.7) like explained later
• set DYLD_LIBRARY_PATH in script which calls python script
• call script like this: DYLD_LIBRARY_PATH=... <script-to-run>
• set DYLD_LIBRARY_PATH in shell (.bash_profile or similar) and call your script with source/. <script-to-run>

Older analysis

What I discovered in my case is following:

• virtualenv setup in relocatable mode (--relocatable) uses /usr/bin/env utility as shebang for django-admin.py:

  #!/usr/bin/env python2.7
  

• env is convenient utility for writing unix scripts that can be runned in various nx environments

• on OSX (I am using macos 10.12 Sierra) for some reason /usr/bin/env hides some system variables visible in parent process - in this case DYLD_LIBRARY_PATH is not transferred to a child process. Test:

  set|grep DYLD_LIBRARY_PATH
  DYLD_LIBRARY_PATH=.../oracle/instantclient_11_2
  
  env|grep DYLD_LIBRARY_PATH
  # nothing
  

• one more check::

  python -c "import os; print os.environ.get('DYLD_LIBRARY_PATH')"
  .../instantclient_11_2:/usr/local/opt/openssl/lib
  
  # put the same line in first line of django-admin.py 
  # and you will get no output for DYLD_LIBRARY_PATH 
  

• interesting is that on Linux (e.g. CentOS) this is not the case, DYLD_LIBRARY_PATH and LD_LIBRARY_PATH are visible in child processes

• because of this cx_oracle.so can not load needed oracle libraries it fails with error:

  Unable to acquire Oracle environment handle
  

The SOLUTION is in changing shebang in django-admin.py:

• from:

  #!/usr/bin/env python2.7
  

• to something like (check your python venv installation: which python):

  #!.../venvs/project11/bin/python2.7
  

• it works like this too - but is less flexible than previous solution::

  #!/usr/bin/env DYLD_LIBRARY_PATH=<put-full-path->/instantclient_11_2 python2.7
  

• or the easiest do not use relocatable virtualenv setup in OSX when using cx_Oracle or any other DYLD_LIBRARY_PATH dependent modules