50

I installed the library and when trying to access SQL in jupyter notebook with my credentials the following error appears:

DatabaseError: DPI-1047: Cannot locate a 64-bit Oracle Client library: "The specified module could not be found". See https://oracle.github.io/odpi/doc/installation.html#windows for help

Rexilife
  • 565
  • 1
  • 4
  • 7
  • That error suggests that you haven't installed a library properly. Did you follow the link it gave you? – TechPerson May 13 '19 at 20:30
  • Check that Python, cx_Oracle and your Oracle Client libraries are all 64-bit or all 32-bit. How do i check this? – Rexilife May 13 '19 at 20:49
  • The error message already tells you you have a 64-bit Python. Follow the instructions in the URL in the message – Christopher Jones May 14 '19 at 01:24
  • The easiest solution now is to use the renamed, latest version of cx_Oracle since it doesn't need Oracle Client libraries. With the default install you won't see DPI-1047 errors. See the [release announcement](https://cjones-oracle.medium.com/open-source-python-thin-driver-for-oracle-database-e82aac7ecf5a). The driver got renamed to python-oracledb. You can install with `pip` from https://pypi.org/project/oracledb/. – Christopher Jones Jul 05 '22 at 23:04
  • Its worth noting you do need the additional Oracle client libraries, if your python-oracledb client needs to run in Thick mode. – Ralph Willgoss Jul 19 '22 at 11:29

12 Answers12

60

The easiest solution is as follows:

  1. Download 64-bit version of oracle instantClient from: https://www.oracle.com/database/technologies/instant-client/winx64-64-downloads.html
  2. Copy the dll files in the instantclient directory to the python directory, as shown below

enter image description here

enter image description here

That is it!

Naik
  • 1,085
  • 8
  • 14
  • 3
    That's very useful. Is there a simple way to get the same issue to resolve in UNIX ? – user29496 Nov 18 '20 at 13:49
  • 2
    On Linux you have to set the library search path (e.g with ldconfig or LD_LIBRARY_PATH - see the Instant Client installation instructions on the download page) before the Python process starts – Christopher Jones Nov 28 '20 at 01:25
  • 4
    @Naik: This is a bad practice. Instead pass the location of the library files to Python as outlined in the cx_Oracle [documentation](https://cx-oracle.readthedocs.io/en/latest/user_guide/initialization.html#using-cx-oracle-init-oracle-client-to-set-the-oracle-client-directory). See my answer below for details. – Stan Nov 30 '20 at 22:02
  • @Stan Thanks for your comment. But that did not work for me! – Naik Dec 02 '20 at 01:58
  • 5
    Using `init_oracle_client(lib_dir=...)` works on Windows and macOS but won't work on Linux. This is documented. – Christopher Jones Jan 29 '21 at 04:39
  • Didn"t like this hack, but this is the easiest solution I came over with. – Musa Feb 23 '21 at 05:37
  • This solution worked for me, why is this bad practice? – bart cubrich Oct 18 '21 at 16:53
  • The easiest solution now is to use the renamed, latest version of cx_Oracle since it doesn't need Oracle Client libraries. See the [release announcement](https://cjones-oracle.medium.com/open-source-python-thin-driver-for-oracle-database-e82aac7ecf5a). The driver got renamed to python-oracledb. You can install with `pip` from https://pypi.org/project/oracledb/. – Christopher Jones Jul 05 '22 at 23:03
  • I'm not 100% sure on this but I think the reason it works is that Python is looking in the PATH environmental variable for the location of the oracle drivers. The python folder itself is in the path. Probably also Python looks at it's own folder. – Rob Aug 29 '22 at 15:32
  • Ugh, fiddling with a Python install directory? Then when you upgrade everything mysteriously breaks and you have to figure out which .DLLs you need... much better to use the answers that involve using `init_oracle_client` to keep things clean. – Eric Kramer May 03 '23 at 18:33
37

The short answer is: cx_Oracle.init_oracle_client(lib_dir= r"c:\path_to_libraries")

Here are the steps that I followed to solve this same issue:

If you have not already installed cx_Oracle you can do so with the following command:
python -m pip install cx_Oracle --upgrade

The cx_Oracle documentation can be found here.

Use the following commands to verify that everything is installed and recognized:

import sqlalchemy as sqla
import pandas as pd
import cx_Oracle

# Test to see if it will print the version of sqlalchemy
print(sqla.__version__)    # this returns 1.2.15 for me

# Test to see if the cx_Oracle is recognized
print(cx_Oracle.version)   # this returns 8.0.1 for me

# This fails for me at this point but will succeed after the solution described below
cx_Oracle.clientversion()

At this point I get errors saying the libraries cannot be located. Here is the solution:

import os
import platform

# This is the path to the ORACLE client files
lib_dir = r"C:\put_your_path_here\instantclient-basic-windows.x64- 19.9.0.0.0dbru\instantclient_19_9"

# Diagnostic output to verify 64 bit arch and list files
print("ARCH:", platform.architecture())
print("FILES AT lib_dir:")
for name in os.listdir(lib_dir):
    print(name)

Be sure to update the lib_dir path specific to your installation. If you have the correct path, you should see a listing of all the Oracle files like: (adrci.exe, oci.dll, oci.sym, etc). This is the location that Python needs to be able to find the Oracle drivers.

The current (Nov 2020) standard way for passing the location of the Oracle libraries for Windows is cx_Oracle.init_oracle_client(lib_dir= r"c:\path_to_libraries"). Here is an example:

lib_dir = r"C:\put_your_path_here\instantclient-basic-windows.x64- 19.9.0.0.0dbru\instantclient_19_9"

try:
    cx_Oracle.init_oracle_client(lib_dir=lib_dir)
except Exception as err:
    print("Error connecting: cx_Oracle.init_oracle_client()")
    print(err);
    sys.exit(1);

At this point I can run the following code without any errors:

# This works after passing the lib_dir path
cx_Oracle.clientversion()    # For me it returns: (19, 9, 0, 0, 0)

DEPRECATED Here is how to update the PATH variable temporarily:

The following works, but using cx_Oracle.init_oracle_client(lib_dir= r"c:\path_to_libraries") is now the preferred way.

import os   
# Manually append the location of the ORACLE libraries to the PATH variable
os.environ["PATH"] = lib_dir + ";" + os.environ["PATH"]
Faizi
  • 410
  • 1
  • 8
  • 13
Stan
  • 905
  • 9
  • 20
  • 3
    The new 'standard' is to use cx_Oracle 8's `init_oracle_client()` instead of setting PATH - see https://cx-oracle.readthedocs.io/en/latest/user_guide/initialization.html – Christopher Jones Nov 28 '20 at 01:26
15

As per documentation accessed on cx_Oracle page.

Step 1: install cx_Oracle

python -m pip install cx_Oracle --upgrade

Step 2: Download and extract Oracle Basic Client

For Windows download and extract Oracle Basic Instatnt client instantclient-basic-windows.x64-19.9.0.0.0dbru.zip file.

Step 3: Inform cx_Oracle module about the Instatnt Client location.

If you stick to documentation and extract them in c:\oracle folder then your script may look like this.

    import cx_Oracle
    cx_Oracle.init_oracle_client(lib_dir=r"C:\oracle\instantclient_19_9")

Now you will be free from the error.

kris
  • 153
  • 1
  • 7
3

I faced this error in Anconda Spyder.

This is how I fixed it.

  1. Get the basic package instantClient from: https://www.oracle.com/database/technologies/instant-client/winx64-64-downloads.html

  2. Extract and copy all the *.dll files and paste it to Anaconda3 folder where you have python.exe.

dhina karan
  • 211
  • 2
  • 4
  • 1
    With cx_Oracle 8 on Windows, I'd recommend using [`init_oracle_client()`](https://cx-oracle.readthedocs.io/en/latest/user_guide/initialization.html#using-cx-oracle-init-oracle-client-to-set-the-oracle-client-directory) since you can keep Instant Client & Python separate. – Christopher Jones Mar 26 '21 at 23:31
3

For MAC

After you do : python -m pip install cx_Oracle --upgrade

try:

import cx_Oracle
# Test to see if the cx_Oracle is recognized
print(cx_Oracle.version)   # this returns 8.0.1 for me

# This fails for me at this point but will succeed after the solution described below
cx_Oracle.clientversion()

if you encounter issues like :

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
cx_Oracle.DatabaseError: DPI-1047: Cannot locate a 64-bit Oracle Client library: "dlopen(libclntsh.dylib, 1): image not found". See https://cx-oracle.readthedocs.io/en/latest/user_guide/installation.html for help

Follow the step highlighted here: https://cx-oracle.readthedocs.io/en/latest/user_guide/installation.html#manual-installation

Manual Installation

  1. Download the Basic 64-bit DMG from Oracle. (https://www.oracle.com/database/technologies/instant-client/macos-intel-x86-downloads.html)

  2. Mount the dng

  3. Open the mounted dng, and run ./install_ic.sh (via terminal) e.g. : cd /Volumes/instantclient-basic-macos.x64-19.8.0.0.0dbru/ && ./install_ic.sh

  4. In Finder, eject the mounted Instant Client package.

Re-run the oracle connection.

import cx_Oracle

cx_Oracle.init_oracle_client(lib_dir="/Users/priyank.pathak/Downloads/instantclient_19_8")
## cx_Oracle.init_oracle_client(lib_dir="/Users/your_username/Downloads/instantclient_19_8")

cx_Oracle.clientversion()
Priyank Pathak
  • 464
  • 4
  • 17
  • 1
    Can you update your solution and replace the pip install of mysql packages with the relevant cx_Oracle install? Also, the instructions miss the important step of how cx_Oracle knows where Instant Client is: on macOS use `init_oracle_client()` – Christopher Jones Jun 09 '21 at 23:00
  • 1
    This works. Note that the command `./install_ic.sh` does nothing but move everything from that directory to the `Downloads` folder of your mac. Now we have to manually add this path in the program using the command `init_oracle_client()` as shown in the answer. – Prasannjeet Singh Sep 28 '21 at 11:44
  • I am following these instructions on Mac but still getting he following error: DatabaseError: DPI-1047: Cannot locate a 64-bit Oracle Client library: "dlopen(libclntsh.dylib, 0x0001): tried: 'libclntsh.dylib' (no such file), '/usr/lib/libclntsh.dylib' (no such file), '/Users/balikuma/Desktop/libclntsh.dylib' (no such file)". – Vikram Apr 25 '22 at 15:44
2

Make sure you have installed the correct oracle client (you can find the oracle client package from here "https://www.oracle.com/in/database/technologies/instant-client/winx64-64-downloads.html" and add the downloaded folder inside the folder where python is installed and then add this location (location of your client package folder) to system's environment variable. Hope that will work.

jhalak
  • 51
  • 2
2

I suggest you to first check the compatibility of your OS, Python and Oracle Instant Client architecture:

import platform
platform.architecture()

Then, I definitely advise you to set the Oracle Instant Client inside your jupyter notebook:

import os
os.environ["PATH"] = "Complete Location of Instant Client Folder" + ";" + os.environ["PATH"]
Mohammad-Reza Malekpour
  • 787
  • 10
  • 18
  • 2
    Note PATH is only for Windows. And it has been superseded by using cx_Oracle 8's `init_oracle_client(lib_dir=xxx)` (which is useful on Windows and macOS) – Christopher Jones Nov 28 '20 at 01:24
1

I went into the same problem but on Arch Linux, running in NodeJS. Some of the answers here helped me in searching for the official installation guide. Then I followed these steps and they worked for me: https://oracle.github.io/node-oracledb/INSTALL.html#instzip , hope it could help someone else too.

  1. Download Instant Client Basic Light Package (ZIP) (Linux) https://www.oracle.com/database/technologies/instant-client/linux-x86-64-downloads.html

  2. Create folder and unzip into it

    mkdir /opt/oracle
unzip ~/Downloads/instantclient-basiclite-linux.x64-21.4.0.0.0dbru.zip -d /opt/oracle/

  3. Then, according to the docs, execute these commands below:

    If there is no other Oracle software on the machine that will be impacted, then permanently add Instant Client to the run-time link path. For example, if the Basic package unzipped to /opt/oracle/instantclient_19_11, then run the following using sudo or as the root user

    sudo sh -c "echo /opt/oracle/instantclient_19_11 > /etc/ld.so.conf.d/oracle-instantclient.conf"
sudo ldconfig

    OR

    Alternatively, every shell running Node.js will need to have the link path set:

    export LD_LIBRARY_PATH=/opt/oracle/instantclient_19_11:$LD_LIBRARY_PATH
Guilherme Abacherli
  • 305
  • 4
  • 12
  • This worked for me! Thanks a lot. I am working under WSL Suse Leap Distro. Needed the sudo ldconfig solution you meantioned under point 3 for SQLTools Extension in VSCode IDE to work successfully with Oracle Database Driver. My error message before the fix in VSCode was: DPI-1047: Cannot locate a 64-bit Oracle Client library ... libclntsh.so: cannot open shared object file – snukone Apr 27 '23 at 16:32
0

Probably you have installed cx_Oracle Python library. In order to execute the DB connectivity through jupyter notebook, you need to install the Oracle Client and that is what missing in your scenario. Please follow the steps from below link and and install Oracle Client and this will solve your problem: https://oracle.github.io/odpi/doc/installation.html#windows

Surya Ambarkar
  • 31
  • 2
0

This might help someone: I also had the same error and I read through the installation documentation https://cx-oracle.readthedocs.io/en/latest/user_guide/installation.html (I was using a zip for installation so I focused on section: Oracle Instant Client Zip Files)

And I was missing a "yum install -y libaio" (used 'centos:7' base image) after unzipping the oracle client to /opt/oracle and also added the following envs:

ENV ORACLE_HOME /opt/oracle/instantclient_19_3 ENV DYLD_LIBRARY_PATH /opt/oracle/instantclient_19_3 ENV LD_LIBRARY_PATH /opt/oracle/instantclient_19_3 ENV TNS_ADMIN /opt/oracle/instantclient_19_3

P. Sithole
  • 151
  • 1
  • 13
0

I just installed Sql Developer from Oracle and ran it for the first time, and the install added what was needed and I was able to get through that error. This was on windows. Install below, just extract it when it installs and run the .exe in the main folder. Much simpler than the other solutions!

https://www.oracle.com/database/sqldeveloper/technologies/download/

Taylor Belk
  • 162
  • 1
  • 16
0

You just need to put the Oracle Instant Client folder in your PATH environment variable. Remember to restart whatever application that needs the instant client after updating the PATH variable.

If you installed the instant client at: c:\oracle\instantclient_21_8, simply put this folder in your PATH environment variable.

Copying the instant client files to Python folder is a very bad idea, since if you have other python versions installed in the future you will have to remember duplicating de instant client files. If you upgrade the instant client, you will need to keep these duplicated files updated too.

Fabio Vaz
  • 1
  • 2