cx_Oracle.InterfaceError: Unable to acquire Oracle environment handle

[faceGitHub]

I know I am in a grey area here but I thought I would give it shot.
Any help would be greatly appreciated. If there is a better venue for this please advise.

cx_Oracle works perfectly from IDLE and sqlplus does also

I am trying to get it to work with PyCharm

/Library/Frameworks/Python.framework/Versions/3.6/bin/python3.6 /Applications/PyCharm.app/Contents/helpers/pydev/pydevconsole.py 51803 51804
PyDev console: starting.
import sys;
print('Python %s on %s' % (sys.version, sys.platform))
sys.path.extend(['/Users/Face/PycharmProjects/PyTest'])
Python 3.6.1 (v3.6.1:69c0db5050, Mar 21 2017, 01:21:04)
[GCC 4.2.1 (Apple Inc. build 5666) (dot 3)] on darwin
import os
os.environ['DYLD_LIBRARY_PATH'] = '/Users/Face/instantclient_12_1'
os.environ['LD_LIBRARY_PATH'] = '/Users/Face/instantclient_12_1'
import sys
sys.path.append("/usr/local/lib")
sys.path.append("/Users/Face/instantclient_12_1")
print(sys.path)
['/Applications/PyCharm.app/Contents/helpers/pydev', '/Users/Face/instantclient_12_1', '/Users/Face/anaconda/lib/python3.6/site-packages', '/Applications/PyCharm.app/Contents/helpers/pydev', '/Library/Frameworks/Python.framework/Versions/3.6/lib/python36.zip', '/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6', '/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/lib-dynload', '/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages', '/Users/Face/anaconda/lib/python3.6/site-packages/IPython/extensions', '/Users/Face/anaconda/lib/python3.6/site-packages/IPython/extensions', '/Users/Face/PycharmProjects/PyTest', '/usr/local/lib', '/Users/Face/instantclient_12_1']

import cx_Oracle
print(cx_Oracle.clientversion())
(12, 1, 0, 2, 0)
ip_addr = '192.168.1.19'
port = 1521
sid = 'xe'
dsn_tns = cx_Oracle.makedsn(ip_addr, port, sid)
db = cx_Oracle.connect('xxxxx', 'xxxxx', dsn_tns)
Traceback (most recent call last):
File "", line 5, in
cx_Oracle.InterfaceError: Unable to acquire Oracle environment handle

1. What is your version of Python? Is it 32-bit or 64-bit?
   3.6 64bit
2. What is your version of cx_Oracle?
   Client versions and database are aligned
3. What is your version of the Oracle client (e.g. Instant Client)? How was it
   installed? Where is it installed?
   (12, 1, 0, 2, 0), Client downloaded from Oracle website, cx was installed via PIP, /Users/Face/instantclient_12_1'
4. What is your version of the Oracle Database?
   Same
5. What is your OS and version?
   El Capitan 10.11.6
6. What compiler version did you use? For example, with GCC, run
   gcc --version.
   GCC 4.2.1
7. What environment variables did you set? How exactly did you set them?
   See above since PyCharm doesn't see user environmental variables
8. What exact command caused the problem (e.g. what command did you try to
   install with)? Who were you logged in as?
   When I try to run the application
9. What error(s) you are seeing?
   cx_Oracle.InterfaceError: Unable to acquire Oracle environment handle

[jmcp]

According to the build instructions in v5.2/5.2.1/5.3, running successfully on macosx requires setting the ORACLE_HOME environment variable, to the location where the instant client libraries can be found.

If you set that variable in your env and then run through your tests, does it work?

[cjbj]

@anthony-tuininga we were talking about improving install doc for macOS yesterday??

@faceGitHub I believe you'll need FORCE_RPATH with cx_Oracle 5. However you missed the answer to question "What is your version of cx_Oracle?" so you may be using cx_Oracle 6?

Also DYLD_LIBRARY_PATH and ilk were deliberately broken by Apple's SIP changes in recent macOS versions, so no need to set them. The new rule of thumb on macOS, is that 'when in doubt, put Instant Client in ~/lib or /usr/local/lib'

[faceGitHub]

Thank you for the quick response. Sorry for missing that data item.
I am using version 5.3 of cx_Oracle and setting ORACLE_HOME to the install location has no effect.
I set it via os.environ['ORACLE_HOME'] = '/Users/Face/instantclient_12_1'

[anthony-tuininga]

Do you set it before or after you import cx_Oracle? Can you put the instant client libraries in ~/lib instead and see if that helps? See this page: https://oracle.github.io/odpi/doc/installation.html#macos. It is designed for ODPI-C (used by cx_Oracle 6) but the content is generally relevant for any OCI program.

[cjbj]

In cx_Oracle, ORACLE_HOME is (or was in cx_Oracle 5) only used during install. Since you have it installed, I'm not surprised at what you saw.

Note that setting Oracle environment variables in Python (or any language) script is error prone and not recommended. See #36

[faceGitHub]

Thank you for the comment. I agree that I have used an initial poor practice setting the environmental vars in script, but for my proof of principle testing I will take some shortcuts. The design will be optimized and re-coded.
So should I basically copy/move the install to /usr/local/lib or reinstall with FORCE or go to 6?
MacOS doesn not like changes to the usr/lib directory. Even rooting it, it complains.

[anthony-tuininga]

Take a look at the page I referenced. There are some instructions there which should work. Note that we are not suggesting changes to /usr/lib but to ~/lib (in your home directory)! You can copy or simply create a symlink and you will not need to use FORCE_RPATH (but using FORCE_RPATH is a solution if you prefer it).

[faceGitHub]

I missed that detail about ~/lib - my brain was thinking that the context that PyCharm would execute in would not see it. My apologies. Again thank you both for your help and I am reading the reference as you prescribed.

[faceGitHub]

You are the best! I got a cursor and data. Creating the ~/lib directory and copying the DLs there worked great. I can't thank you enough.

[faceGitHub]

This is what I followed for the client Installation from the Oracle site:
Installing Instant Client 12.1 on macOS
Instant Client 12.1.0.2 supports macOS El Capitan, Yosemite and Mavericks.

1. Download the desired Instant Client ZIP files. All installations require the Basic or Basic Light package.
2. Unzip the packages into a single directory such as "~/instantclient_12_1". For example, to use SQL*Plus:
   cd ~
   unzip instantclient-basic-macos.x64-12.1.0.2.0.zip
   unzip instantclient-sqlplus-macos.x64-12.1.0.2.0.zip
3. Create the appropriate libclntsh.dylib link for the version of Instant Client. For example:
   cd ~/instantclient_12_1
   ln -s libclntsh.dylib.12.1 libclntsh.dylib
   Note: OCCI programs will additionally need:
   ln -s libocci.dylib.12.1 libocci.dylib
4. To be able to run SQL*Plus, update PATH. For example:
   export PATH=~/instantclient_12_1:$PATH
5. Run SQL*Plus and connect using your database credentials and connection string:
   sqlplus hr/welcome@localhost/orcl

Note: Custom applications should link with -rpath set to the directory containing Instant Client 12.1.
My understanding or lack of understanding of the implications of that last line :( ?

[faceGitHub]

Ok what kind of beer or wine am I sending?

[anthony-tuininga]

You are the best! I got a cursor and data. Creating the ~/lib directory and copying the DLs there worked great. I can't thank you enough.

You're very welcome! Glad you got it figured out.

Note: Custom applications should link with -rpath set to the directory containing Instant Client 12.1.
My understanding or lack of understanding of the implications of that last line :( ?

When you link your application, you use an option like this: -Wl,-rpath,<dir>, where <dir> is replaced by the directory containing the Instant Client 12.1 libraries.

[faceGitHub]

Thanks again for all your help it was very appreciated.

Paul