Issue
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?
Solution
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")
Answered By - Aqua_George Answer Checked By - Dawn Plyler (WPSolving Volunteer)