Connect to Oracle database using python from a Linux server (lxv)-CodePudding

In my local machine

I have created a script in python that retrieves data from an Oracle database. The connection to the DB is done using cx_Oracle:

con = cx_Oracle.connect (username, password, dbService)

When using SQL developer the connection is established using custom JDBC.

Replicate procedure on a Linux server.

I have created a python virtual environment with cx-Oracle pip installed in it.
I have Oracle Client 19.3.0 installed in the server, and the folder instantclient is in place.

When I try to execute the python script as is I get the following error.

cx_Oracle.DatabaseError: DPI-1047: Cannot locate a 64-bit Oracle Client library: DPI-1047: Cannot locate a 64-bit Oracle Client library: "libclntsh.so: cannot open shared object file: No such file or directory". See https://cx-oracle.readthedocs.io/en/latest/user_guide/installation.html for help

I assumed that the problem was the Oracle path which is not the one that python expected. So, I added this extra line of code pin-pointing the path where the Oracle libraries are located.

cx_Oracle.init_oracle_client(lib_dir=r"/apps/oracle/product/19.3.0/lib")

This leads to a different error:

cx_Oracle.DatabaseError: Error while trying to retrieve text for error ORA-01804

Any clues?

CodePudding user response：

The cx_Oracle initialization doc points out that on Linux init_oracle_client() doesn't really do what you think it does. You must still set the system library search path to include the Oracle libraries before the Python process starts.

Do I understand correctly that the machine with Python has both the DB installed and Instant Client??

If you do want Python to use the Instant Client libraries, then set LD_LIBRARY_PATH to its location and do not set ORACLE_HOME.

If you have a full Oracle DB installation on the machine with Python, then you can delete Instant Client. You need to set ORACLE_HOME, LD_LIBRARY_PATH and whatever else is needed before starting Python - in general run source /usr/local/bin/oraenv. This should set the system library search path to include /apps/oracle/product/19.3.0/lib . A code snippet like this (untested) one may help: export ORACLE_SID=ORCLCDB;set ORAENV_ASK=NO; source /usr/local/bin/oraenv. Make sure the Python process has read access to the ORACLE_HOME directory.

The cx_Oracle installation guide discusses all this.

CodePudding user response：

The answer to my question was indicated by the ORA-1804 message.

According to the Oracle initialization doc: https://cx-oracle.readthedocs.io/en/latest/user_guide/initialization.html#usinginitoracleclient

Note if you set lib_dir on Linux and related platforms, you must still have configured the system library search path to include that directory before starting Python.

On any operating system, if you set lib_dir to the library directory of a full database or full client installation, you will need to have previously set the Oracle environment, for example by setting the ORACLE_HOME environment variable. Otherwise you will get errors like ORA-1804. You should set this, and other Oracle environment variables, before starting Python, as shown in Oracle

Even though defining the ORACLE_HOME should be done before starting Python (according to Oracle documentation) it is possible to do so by modifying the python script itself. So before the oracle client initialization command the following commands had to be added:

import os
# Setup Oracle paths 
os.environ["ORACLE_HOME"] = '/apps/oracle/product/19.3.0'
os.environ["ORACLE_BASE"] = '/apps/oracle'
os.environ["ORACLE_SID"] = 'orcl'
os.environ["LD_LIBRARY_PATH"] = '/apps/oracle/product/19.3.0/lib'

import cx_Oracle
# Initialize Oracle client
cx_Oracle.init_oracle_client(lib_dir=r"/apps/oracle/product/19.3.0/lib")