Unable to use CX_Oracle library in notebook

[User16753724663]

While using cx_oracle python library, it returns the below error:

error message: Cannot locate a 64-bit Oracle Client library: "libclntsh.so: cannot open shared object file: No such file or directory

The cx_oracle library is dependent on native libraries like libclntsh.so. There is an instant client required to use the cx_oracle library.

This often breaks due to the unavailability of the instant client or missing this library in the path variable.

[User16753724663]

To fix this issue, we can use the below init script to download the instant client and add that in the path variable. Sometimes the dynamic linker run-time cache needs to be refreshed, especially if there are native libraries that have cross dependencies, and they were placed on the machine before the LD_LIBRARY_PATH was set. In this case, run ldconfig -v /path/to/native/libs before attempting to load the native library from Java.

Here is a sample init script that would help

Step 1: Create the base directory you want to store the init script ( assuming it does not exist.) Here we use dbfs:/databricks/<directory> as an example.

              dbutils.fs.mkdirs("dbfs:/databricks/<directory>/")

Step 2 : Create the script 

 
 
dbutils.fs.put("dbfs:/databricks/oracleTest/oracle_ctl.sh","""
 
 
 
#!/bin/bash
 
 
 
#Download instant client archived file and update the url in case of different #version
 
 
 
wget --quiet-O /tmp/instantclient-basiclite-linux.x64-19.3.0.0.0dbru.zip https://download.oracle.com/otn_software/linux/instantclient/193000/instantclient-basiclite-linux.x6...
 
 
 
unzip /tmp/instantclient-basiclite-linux.x64-19.3.0.0.0dbru.zip -d /databricks/driver/oracle_ctl/
 
 
 
sudo echo 'export LD_LIBRARY_PATH="/databricks/driver/oracle_ctl/"' >> /databricks/spark/conf/spark-env.sh
 
 
 
sudo echo 'export ORACLE_HOME="/databricks/driver/oracle_ctl/"' >> /databricks/spark/conf/spark-env.sh
 
 
 
""", True)

Step 3: Verify that the script exists.

              display(dbutils.fs.ls("dbfs:/databricks/<directory>/oracle_ctl.sh"))

Step 4. Configure a cluster-scoped init script in the cluster 

• On the cluster configuration page, click the Advanced Options toggle.
• At the bottom of the page, click the Init Scripts tab.
• In the Destination drop-down, select DBFS, provide the file path to the script, and click Add.

Step 5: Restart the cluster

[AshvinManoj]

This did not work for me. I get an error as

DPI-1047: Cannot locate a 64-bit Oracle Client library: "/databricks/driver/oracle_ctl//lib/libclntsh.so: cannot open shared object file: No such file or directory".

When I tried to check for the directory using dbutils.f.ls("/databricks/driver/oracle_ctl") I am not able to find that directory. May be the init script is not copying the client as expected. So I also manually downloaded the Oracle client and mapped it to my cluster by creating a location as "/databricks/driver/oracle_ctl/", still no success.

Also I noticed that the error is pointing to a location "..../oracle_ctl//lib/libclntsh". When I inspected the downloaded client, I am not able to find any folder called /lib/libclntsh. May be its pointing to a wrong directory because of any recent changes?

Any helps is appreciated to connect to Oracle database in on premises system.

[User16752245772 · Accepted solution]

Hi @Manoj Ashvin​ can you use the below init script and try ?

dbutils.fs.put("dbfs:/databricks/oracleTest/oracle_ctl_new.sh","""
 
#!/bin/bash
sudo apt-get install libaio1
wget --quiet -O /tmp/instantclient-basiclite-linuxx64.zip https://download.oracle.com/otn_software/linux/instantclient/instantclient-basiclite-linuxx64.zip
unzip /tmp/instantclient-basiclite-linuxx64.zip -d /databricks/driver/oracle_ctl/
mv /databricks/driver/oracle_ctl/instantclient* /databricks/driver/oracle_ctl/instantclient
sudo echo 'export LD_LIBRARY_PATH="/databricks/driver/oracle_ctl/instantclient/"' >> /databricks/spark/conf/spark-env.sh
sudo echo 'export ORACLE_HOME="/databricks/driver/oracle_ctl/instantclient/"' >> /databricks/spark/conf/spark-env.sh 
 
""", True)

[AshvinManoj]

Hello @Manoj Hegde​ ,

Thanks for your suggestion. I used the script and the error disappeared. Hurray!

[ovbieAmen]

Hi @AshvinManoj  I used your script and still get same error.  

sudo echo 'LD_LIBRARY_PATH="/dbfs/databricks/instantclient_23_6"' >> /databricks/spark/conf/spark-env.sh

sudo echo 'ORACLE_HOME="/dbfs/databricks/instantclient_23_6"' >> /databricks/spark/conf/spark-env.sh

oracledb.init_oracle_client(lib_dir=/dbfs/databricks/instantclient_23_6)