cx_Oracle.DatabaseError: DPI-1047

[grazielegs]

Answer the following questions:

What is your version of Python? Is it 32-bit or 64-bit? 3.6.1, 64-bit.

What is your version of cx_Oracle? 5.0.3

What is your OS and version? Windows 7.

What environment variables did you set? How exactly did you set them? Just the environment variables to execute Python. How I set for cx_Oracle?

What exact command caused the problem (e.g. what command did you try to install with)? Who were you logged in as? When I tried to execute a simple script to check the Oracle connection.

What error(s) you are seeing? cx_Oracle.DatabaseError: DPI-1047: 32 bit Oracle Client library cannot be loaded: Não foi possível encontrar o módulo especificado. See https://oracle.github.io/odpi/doc/installation.html#windows for help

I'm trying to connect the Oracle in Python, I've installed the cx_Oracle (pip install cx_Oracle). Someone could help please? I read some threads here and googled this error information but nothing helps at the moment.

[anthony-tuininga]

From the error message you are getting it would seem that you have some conflicts with your answers! The error message mentioned is only found in cx_Oracle 6 and the particular value you are mentioning is only found in cx_Oracle 6.0rc2! In addition, it is stating that you need to have 32-bit Oracle Client libraries but you claim to be running 64-bit Python! Can you clarify those points?

Did you follow the URL mentioned in the error message? (https://oracle.github.io/odpi/doc/installation.html#windows)

Note that you will need an Oracle client. The simplest Oracle client you can install is the instant client. See the URL above for more information.

[grazielegs]

Yeah, I understood about the fact I'm using a 32-bit Oracle library and 64-bit Python, but it was confuse about where I set that? Just installing the Oracle client? I read that issue https://github.com/oracle/python-cx_Oracle/issues/41 and realize is the same error but don't explain what I could do to set which OCI.dll is being loaded. I will start again the installation with the url above. Thanks for answer :)

[Juriell]

Hi. I have a similar problem. I used these instructons: http://cx-oracle.readthedocs.io/en/latest/installation.html

I'm using: python 3.6 on a windows 10 64-bit system. The python is also 64-bit I did the following: 1) Installed cx_oracle 6.0 using command: python -m pip install cx_Oracle --upgrade --pre. 2) I downloaded oracle instantclient 12.1 64-bit and unzipped it 3) I added the following path to PATH variable (the path where oci.dll is located): C:\instantclient-basic-windows.x64-12.1.0.1.0\instantclient_12_1 4) I have Visual studio 2010 (to be exact visual c++ 2010) redistributable installed but it is not in the PATH variable. I don't know if it has to be. Followed the instructions from here: https://oracle.github.io/odpi/doc/installation.html#windows

Sadly I still get the message "cx_Oracle.DatabaseError: DPI-1047: 64 bit Oracle Client library cannot be loaded: The specified module could not be found. See https://oracle.github.io/odpi/doc/installation.html#windows for help" when running "Import cx_Oracle"command.

I hope someone can help.

[anthony-tuininga]

My suggestion: try installing the SQL*Plus package as well and ensure that you can connect to a database.

[Juriell]

Do you mean a python package for SQL Plus or ORACLE Instant Client Package - SQL*Plus?

[anthony-tuininga]

Oracle Instant Client Package in this case!

[Juriell]

Ok, i did it. but there was more to be done so i just post it here if someone else has this problem. Here is the complete process i did: 1) downloaded ORACLE Instant Client Package - SQL*Plus and unzipped it to C:/ instantclient-sqlplus-windows.x64-12.1.0.2.0/instantclient_12_1 and tried to run it. Nothing - the CMD window just blinked and the command "sqlplus" was not recognized by windows main CMD. 2) Noticed that the paths C:\instantclient-basic-windows.x64-12.1.0.1.0\instantclient_12_1 and C:/ instantclient-sqlplus-windows.x64-12.1.0.2.0/instantclient_12_1 have the same subfolder so i copied everything together into instantclient_12_1 folder and moved it out to C:/instantclient_12_1 3) Modified the PATH variable from C:\instantclient-basic-windows.x64-12.1.0.1.0\instantclient_12_1 to C:/instantclient_12_1 and ran sqlplus - it worked and asked for a username 4) This was my first time with SQL Plus so i needed about 15 minutes of googling and some username/password tries to understand that it does not see a database 5) The next logic step was to install a local database on my PC so i did it. And after 1 hour i had an ORACLE 12c database, i could log into it with SQL Plus and i could execute import cx_Oracle in python with no errors. I did not make a connection in python because it was already late night so that is the next step but i'm on the right track now.

But there are some questions and conclusions to be made: 1) The oracle instant client and placing its path into PATH variable is not enough. I don't know if it is missing a file or i have to change its structure but it is just not working. 2) I did this on my home PC and a local database. Now i need to repeat that at work on approx 10 PCs and connect to a remote DB. How do i do it? I hope i don't have to install a fake local database on every PC just to get cx_Oracle working. 3) The database installation created a new entry in the PATH variable: C:/instantclient_12_1/bin - i set its client to be installed in the same folder as instant client so i guess it was overwritten and i think this entry made the magic happen. But the big question is: exactly what file is cx_Oracle looking for? and should it be placed in a bin folder? These questions must be answered because the cx_Oracle documentation has no info about it.

[anthony-tuininga]

To answer your questions:

1. The instant client is enough. I've used it many times and the only thing necessary is to add the location of OCI.dll to the PATH environment variable -- as long as the VS redistributable is installed, of course! Thus the reason for installing SQL*Plus to ensure that the regular usage of the instant client is working as expected.

2. You do not need to install a local database on each machine. The instant client is sufficient.

3. A local database installation will also work, but it isn't needed. Now that you have done that you can install the instant client on another machine and connect to that database.

[Juriell]

I'm sorry - i'm new to oracle configuration - but i had the instant client installed, the PATH set and SQLPlus working and even then the command import cx_Oracle was not working. Maybe i just needed a database file defined (tnsnames.ora at the right location that i don't know) or maybe i had to somehow feed SQLPlus some database parameters (ip, port, url) - no idea. But there is something the database installation did that made it happen. I don't have another machine at home - just one PC. But i tried this at work and got the same problem.

[anthony-tuininga]

You had SQL*Plus working? You were able to connect to a database? If so, the same connect string should work for cx_Oracle. I'm not sure what the best documentation to read for Oracle configuration might be. Perhaps @cjbj might be able to suggest something.

[Juriell]

Just solved it and it is embarrassing. The only thing i did after the database installation was a PC reboot. When i set the PATH variable for the first i did not do a reboot. Just now i did the following: 1) deleted the environment variables 2) did a reboot 3) ran import cx_Oracle and got the error again 4) set the PATH variable back 5) did a reboot :) 6) ran import cx_Oracle and it works

Such a small detail but a large impact. I will surely remember to do a reboot after changing environment variables. Thanks for the help.

[anthony-tuininga]

Glad you got it figured out! BTW, you should be able to simply restart your command prompt to get the new environment variables. You shouldn't need to reboot -- although that clearly works, too! :-)

[cjbj]

@anthony-tuininga we should add restarting-command-prompt to the troubleshooting notes

[anthony-tuininga]

I added that information to the troubleshooting section.

[tamilselvamc]

https://oracle.github.io/odpi/doc/installation.html#macos - worked for me

[jkornblum]

For anyone who finds themselves here. Comically, I had spaces in my PATH string which will not work. Once I removed the spaces then all the Oracle stuff started working as designed.

I.e. change ...C:\Windows\CCM;C:\Program Files\Git\cmd; C:\oracle\instantclient_12_2; to ...C:\Windows\CCM;C:\Program Files\Git\cmd;C:\oracle\instantclient_12_2;

[guili618]

@tamilselvamc thanks,it's work for me !

[dlenski]

It appears to me that some occurrences of this error represent a regression in 6.1 — or at least a change in the default locations where it looks for the client libraries…?

I have a script which would run correctly as a cronjob with cx-Oracle 5.x, despite the fact that ORACLE_HOME wasn't set correctly in the cronjob.

However, when I upgraded to 6.1 last week, the cronjob started failing. Setting ORACLE_HOME correctly in the cronjob fixes it.

(Obviously, I should've been doing that in the first place, but this is a somewhat surprising change nevertheless.)

EDIT: It's actually LD_LIBRARY_PATH, not ORACLE_HOME, which must be set correctly for the 6.1 library to find the libclntsh.so.

[cjbj]

@dlenski were you were using RPATH linking in 5.2+? This isn't possible with runtime loading of the ODPI used in cx_Oracle 6

[dlenski]

@dlenski were you were using RPATH linking in 5.2+? This isn't possible with runtime loading of the ODPI used in cx_Oracle 6

I'm not sure. Are you saying that the older cx-Oracle libs had a hard-coded rpath including /usr/lib/oracle/12.1/client64/lib, and cx_Oracle+ doesn't?

As far as I can tell, they're both dynamically linked to libclntsh.so, and ldd can find the linkage correctly with no help from LD_LIBRARY_PATH. And yet, the 6.1 library does not work without me including the correct path in LD_LIBRARY_PATH.

# on a system with 5.3 installed
$ LD_LIBRARY_PATH='' ldd /opt/rh/rh-python35/root/usr/lib64/python3.5/site-packages/cx_Oracle.cpython-35m-x86_64-linux-gnu.so
    libclntsh.so.12.1 => /usr/lib/oracle/12.1/client64/lib/libclntsh.so.12.1 (0x00007f83b4890000)
    libclntshcore.so.12.1 => /usr/lib/oracle/12.1/client64/lib/libclntshcore.so.12.1 (0x00007f83b22c4000)

# on a system with 6.1 installed
$ LD_LIBRARY_PATH='' ldd /usr/local/lib/python3.4/dist-packages/cx_Oracle.cpython-34m.so
    libclntsh.so.12.1 => /usr/lib/oracle/12.1/client64/lib/libclntsh.so.12.1 (0x00007f83b4890000)
    libclntshcore.so.12.1 => /usr/lib/oracle/12.1/client64/lib/libclntshcore.so.12.1 (0x00007f83b22c4000)

[cjbj]

Thanks for the correction that it was LD_LIBRARY_PATH, not ORACLE_HOME. And also for showing that you are using Instant Client RPMs.

cx_Oracle 5.2 and 5.3 automatically configured RPATH during install when the Instant Client RPMs were detected by the installer. That's why you didn't need to set LD_LIBRARY_PATH.

cx_Oracle 6 uses ODPI which uses dlopen(). It's not possible with this (and Instant Client - but that's a longer story still being discussed) to automatically locate and load Instant Client libraries. You need to set LD_LIBRARY_PATH or use ldconfig, as you discovered. It's often easier to use ldconfig, as mentioned in various install guides.

This is a small regression. We decided the benefits of ODPI are greater, including that it allows binary Wheels to be distributed.

How would you feel if a future version of Instant Client automatically configured ldconfig during install? This could negatively impact anyone with multiple versions of Instant Client or a full database also installed, since the default library search path would be affected.

[dlenski]

Thanks for explaining, @cjbj. Now that I understand it better, it seems like this was a good change, and once understood it's easy to tweak ldconfig config to find the libraries.

So I really have no legitimate complaints. Just trying to understand what changed.

How would you feel if a future version of Instant Client automatically configured ldconfig during install?

I think that'd be a very good idea, actually. Perhaps it could warn and stop if it found multiple possible Oracle installations, and proceed silently if not.

[gboutoulis]

dynamically calling instantclient through python script seems to solve the problem for me. Try this on top:

def update_os():

    os.environ["PATH"] = "C:\oracle\instantclient_12_2\;"+os.environ["PATH"]
    #print(os.environ["PATH"])
    #os.environ["NLS_LANG"] = ".UTF8"

import os
update_os()
import cx_Oracle

customizing your installation dir ;)

[cjbj]

We generally don't recommend setting library search paths or other Oracle variables at runtime because it leads to more problems than it solves. Each language has its own linking rules, some environment variables are safer to set than others, it's easy for application code to change and the variables end up being set too late, etc etc . But if it works for you . . .

[Update: plus there are OS differences in whether you can set some variables after the process starts]

[dylanking42]

We generally don't recommend setting library search paths or other Oracle variables at runtime because it leads to more problems than it solves. Each language has its own linking rules, some environment variables are safer to set than others, it's easy for application code to change and the variables end up being set too late, etc etc . But if it works for you . . .

[Update: plus there are OS differences in whether you can set some variables after the process starts]

I understand the reasoning here completely, but working from a tightly controlled corp environment made it so I had a very wonky PATH that contained 11.2g oci.dlls, 32 bit. The code snippet that @gboutoulis provided allowed me to dodge them and actually get "select * from all_tab_columns" working IRL. This is a nice work around for me, after many many hours of searching I wouldn't mind seeing it placed a little more prominently in the documentation as a "last resort."

[cjbj]

@dylanking42 As of cx_Oracle 7, you could put the Instant Client libraries in the directory with the cx_Oracle library and avoid having to set PATH - this is documented.

If, instead, you need to play with PATH, then set it in a parent batch script that invokes your app. As I said earlier, setting env vars in application scripts 'leads to more problems than it solves' so I don't want to mention it at all - users will copy and paste without realizing the implications and restrictions.

[Update: E_TOO_MANY_REPOS: I referenced node-oracle here earlier]

[dylanking42]

Fantastic, thank you very much.

[mohammedyunus009]

Firstly i downloaded instantclient-basic-windows.x64-19.3.0.0.0dbru.zip .I had used a temp fix in python. I used below code

import os
os.environ['PATH'] = os.environ['PATH']+r'C:\Users\my_username\Downloads\installed_app\instantclient_19_3;'

[anthony-tuininga]

You should instead use:

import os
os.environ['PATH'] = os.environ['PATH] + os.pathsep + r'C:\Users\my_username\....'

Note the inclusion of os.pathsep. Without it, the last entry in your previous PATH will be clobbered!

[khangvan]

dynamically calling instantclient through python script seems to solve the problem for me. Try this on top:

def update_os():

    os.environ["PATH"] = "C:\oracle\instantclient_12_2\;"+os.environ["PATH"]
    #print(os.environ["PATH"])
    #os.environ["NLS_LANG"] = ".UTF8"

import os
update_os()
import cx_Oracle

customizing your installation dir ;)

Thank you very much. I add PATH manual but seem not work. This is help much to customize PATH modification in correct way. Thank you

[sarment0]

Still not working for me, i did everything posted in this thread, rebooted, changed different versions of instantclient, delete the path ,rebooted, i already used the update_os() thing and the error still...

There is a way to hardcode the address of the client? not to update the windows variable, i mean to force the address in the code...

[cjbj]

That's being looked at for a future version.

[sarment0]

I fixed in the weirdest way possible.

1. I tried to install Oracle Express 11g, but it didnt finish the installation, always rolling back for no reason.
2. At this point i was thinking about set my computer on fire, disabled torrent, closed whatsapp, closed sync files softwares (gdrive, dropbox, mega), only windows alone running.
3. I deleted the ORACLE_HOME and delete the path from PATH in enviroment variables.
4. Not working.
5. installed Anaconda and not working either.
6. reinstalled cx_Oracle, still not working.
7. filled the enviroment variables again.
8. close anaconda terminal, reopen, now is working.
9. Tried to open Windows Power shell and give a try, and IS NOT WORKING
10. I dont know why, but at anaconda terminal is working, but at regular windows terminal is not, and i also tried executing as administrator, and the problem stills the same. cx_Oracle.DatabaseError: DPI-1047: Cannot locate a 64-bit Oracle Client library:

It's possible for Oracle to create a Windows Installer (MSI) ? Not just extract the folder in a random folder, i mean a standard installer to fix all possible issues and prevent this kind of headache... In my case i fixed using anaconda, but i dont think this is a good fix, also, if this problem happens again i have no clue how to fix, i was justing trying everything to fix.

[jaivardhan93]

Error :

C:\Users\Administrator\Desktop\OracleAutomation>python Python 3.8.5 (tags/v3.8.5:580fbb0, Jul 20 2020, 15:43:08) [MSC v.1926 32 bit (Intel)] on win32 Type "help", "copyright", "credits" or "license" for more information.

import cx_Oracle connection_startup = cx_Oracle.connect("/",mode = cx_Oracle.SYSDBA | cx_Oracle.PRELIM_AUTH) Traceback (most recent call last): File "", line 1, in cx_Oracle.DatabaseError: DPI-1047: Cannot locate a 32-bit Oracle Client library: "C:\app\oracle\product\db_home1\bin\oci.dll is not the correct architecture". See https://cx-oracle.readthedocs.io/en/latest/user_guide/installation.html for help

On Windows Server 2019, I am facing the same issue, I have set the below environment variables :

set ORACLE_SID=orcl set ORACLE_BASE=C:\app\oracle set ORACLE_HOME=C:\app\oracle\product\db_home1 Also my PATH variable includes : C:\app\oracle\product\db_home1\bin, which contains "oci.dll"

I have tried rebooting the machine, but no luck. Please suggest.

[cjbj]

@jaivardhan93 there is a strong likelihood that C:\app\oracle\product\db_home1 is 64-bit, hence your error since your Python is 32-bit and needs 32-bit Oracle client libraries. If your Oracle software is 64-bit, then it would be easier to uninstall Python and install a 64-bit Python. The alternative is to use a 32-bit Instant Client.

[jaivardhan93]

Thanks @cjbj . Yep, that was the issue and that too a basic one. I checked my Oracle library but somehow missed to check my python's library !