Add support for ORACLE_HOME

[michael-o]

It would be nice if odpi would support ORACLE_HOME instead of fiddling with LD_LIBRARY_PATH for those who have the database or full client installed.

[anthony-tuininga]

A few comments about this request:

• LD_LIBRARY_PATH is only examined when the process starts so it cannot be changed by the code
• the scripts provided by Oracle to change environment (oraenv) already include code to change LD_LIBRARY_PATH so you shouldn't need to fiddle!
• even if the environment variable ORACLE_HOME was examined and the exact library that needs to be opened found, dlopen() will still fail because libclntsh.so has dependent libraries and DT_RPATH is not set in that library (see https://github.com/oracle/python-cx_Oracle/issues/15 for a further discussion on this issue)

Any comments?

[kikitux]

oraenv works, I am able to compile and run the test like this (vs oracle XE)

ORACLE_BASE=/home/oracle
ORACLE_SID=XE
ORAENV_ASK=NO source oraenv
sqlplus / as sysdba @/home/oracle/odpi/test/sql/SetupTest.sql

cd /home/oracle/odpi/
make
cd /home/oracle/odpi/test/
make

/home/oracle/odpi/test/build/TestSuiteRunner

[kubo]

..., dlopen() will still fail because libclntsh.so has dependent libraries and DT_RPATH is not set in that library.

It is correct as for instant clients. As far as I checked, however, it isn't correct for ORACLE_HOME-based clients on Linux.

$ echo $ORACLE_HOME
/home/ora121/app/ora121/product/12.1.0/dbhome_1
$ readelf -d $ORACLE_HOME/lib/libclntsh.so | grep RPATH
 0x000000000000000f (RPATH)              Library rpath: [/home/ora121/app/ora121/product/12.1.0/dbhome_1/lib]

I made a small C program. https://gist.github.com/kubo/4bbb374e2a3fb05f4a1baef2bf8875ea It could not find libmql1.so as you pointed when the client is an instant client. It could find dependent libraries and worked fine when the client is an ORACLE_HOME-based client.

$ wget https://gist.githubusercontent.com/kubo/4bbb374e2a3fb05f4a1baef2bf8875ea/raw/46e10020fe413e2710fbac1d48bd56a02dc05fea/dln-load.c
$ cc -o dln-load dln-load.c -ldl
$ env | grep LD_LIBRARY_PATH  # verify that LD_LIBRARY_PATH isn't set.
$ ./dln-load /opt/oracle/ic_12102_64/libclntsh.so.12.1 # Instant client
dlopen error: libmql1.so: cannot open shared object file: No such file or directory
$ env | grep ORACLE_HOME
ORACLE_HOME=/home/ora121/app/ora121/product/12.1.0/dbhome_1
$ ./dln-load $ORACLE_HOME/lib/libclntsh.so.12.1 # ORACLE_HOME-based client
OCIEnvCreate() => 0
link map:
 (nil): 
 0x7ffd00cf4000: 
 0x7f54c5798000: /lib/x86_64-linux-gnu/libdl.so.2
 0x7f54c53ce000: /lib/x86_64-linux-gnu/libc.so.6
 0x7f54c599c000: /lib64/ld-linux-x86-64.so.2
 0x7f54c240f000: /home/ora121/app/ora121/product/12.1.0/dbhome_1/lib/libclntsh.so.12.1
 0x7f54c2199000: /home/ora121/app/ora121/product/12.1.0/dbhome_1/lib/libmql1.so
 0x7f54c1e1b000: /home/ora121/app/ora121/product/12.1.0/dbhome_1/lib/libipc1.so
 0x7f54c1711000: /home/ora121/app/ora121/product/12.1.0/dbhome_1/lib/libnnz12.so
 0x7f54c14cc000: /home/ora121/app/ora121/product/12.1.0/dbhome_1/lib/libons.so
 0x7f54c11c3000: /lib/x86_64-linux-gnu/libm.so.6
 0x7f54c0fa6000: /lib/x86_64-linux-gnu/libpthread.so.0
 0x7f54c0d8d000: /lib/x86_64-linux-gnu/libnsl.so.1
 0x7f54c0b85000: /lib/x86_64-linux-gnu/librt.so.1
 0x7f54c0983000: /lib/x86_64-linux-gnu/libaio.so.1
 0x7f54c0411000: /home/ora121/app/ora121/product/12.1.0/dbhome_1/lib/libclntshcore.so.12.1
 0x7f54c0208000: /lib/x86_64-linux-gnu/libnss_compat.so.2
 0x7f54bfffc000: /lib/x86_64-linux-gnu/libnss_nis.so.2
 0x7f54bfdea000: /lib/x86_64-linux-gnu/libnss_files.so.2

[anthony-tuininga]

Thanks, Kubo. Good point about ORACLE_HOME-based clients. That may be worth pursuing.

[michael-o]

@anthony-tuininga

We don't use oraenv since the target databases are remote and are listed in tnsnames.ora and not in /etc/oratab. I do share @kubo's opinion. ORACLE_HOME based clients are linked from objects at install time. RPATH should work:

$ readelf -d /opt/oracle/product/11.2.0/client_1/lib/libclntsh.so

Dynamic section at offset 0x26835e0 contains 28 entries:
  Tag        Type                         Name/Value
 0x0000000000000001 (NEEDED)             Shared library: [libnnz11.so]
 0x0000000000000001 (NEEDED)             Shared library: [libdl.so.2]
 0x0000000000000001 (NEEDED)             Shared library: [libm.so.6]
 0x0000000000000001 (NEEDED)             Shared library: [libpthread.so.0]
 0x0000000000000001 (NEEDED)             Shared library: [libnsl.so.1]
 0x0000000000000001 (NEEDED)             Shared library: [libc.so.6]
 0x0000000000000001 (NEEDED)             Shared library: [libaio.so.1]
 0x000000000000000e (SONAME)             Library soname: [libclntsh.so.11.1]
 0x000000000000000f (RPATH)              Library rpath: [/opt/oracle/product/11.2.0/client_1/lib]

[cjbj]

@michael-o I have my own ideas about what should 'just work'. Can you expand a bit more on the various install cases you think need to be covered so we're in agreement on the direction? Cover install, upgrade of interfaces and upgrades of Oracle client. Installs with ORACLE_HOME, Instant Client ZIPs, Instant Client RPMs are also things to think about.

[michael-o]

@cjbj Of course! We install (GUI installer) on all machines (RHEL6, HP-UX 11.31) the full client from MOSC. Set ORACLE_HOME on a per-user basis and use SQL*Plus + Oracle PRO*C (with C and Fortran). In the next step, we'd like to Use cx_Oracle in Python and continue to use the basic env variables set as before with the two aforementioned tools. Namely, TNS_ADMIN (with Oracle Wallet), NLS_*, ORACLE_HOME. So, at best, we don't need to change anything when we use Python with cx_Oracle 6.0.

[cjbj]

@michael-o what's stopping you setting LD_LIBRARY_PATH?

[michael-o]

Nothing actually, but supporting ORACLE_HOME would make it consistent with the rest of the Oracle world -- and less configuration to distribute.

[michael-o]

@anthony-tuininga So I added $ORACLE_HOME/lib to /etc/ld.conf.d and ran ldconfig and poluted the entire system library path with all third-party libs Oracle is bundling for its database, e.g., libexpat. Definitively not a way to go.

[anthony-tuininga]

Good point. I'll discuss with @cjbj and get back to you.

[cjbj]

Perhaps because I tend to have multiple Oracle libs around, I'd only ever use ldconfig when I have a single Instant Client and nothing else installed.

BTW, I contributed the installation code to cx_Oracle 5.2 to automatically find & use Instant Client RPMs or ORACLE_HOME, so I get the general install simplification desires. Before 5.2 you would always have needed to fiddle during install, and set LD_LIBRARY_PATH to run.

The new ODPI-C layer model bring benefits for building and upgrading in scenarios that haven't been discussed in this issue. We've traded one install simplication for others, such as the ability to ship Python wheels. Can linking be improved - sure. I'd like changes to make it easier for apps that distribute Instant Client in arbitrary product directions. And maybe it could add ORACLE_HOME to the library loading path too.

[anthony-tuininga]

Suggestion implemented. Thanks!