34

I am establishing a connection to oracle 11g which is in a remote server using cx_oracle 7 with python 3.6.7. my OS in Ubuntu 18.04

I have installed oracle instant client library with libclntsh.so but I am not getting the expected output.

here is the code which i am using to connect to the oracle db

connection = cx_Oracle.connect("username/password@host/port")
print (connection.version)
connection.close()

when the script runs i expect to get the connection version instead i am getting the following error message

File "script.py", line 13, in connection = cx_Oracle.connect("username/password@host/port") cx_Oracle.DatabaseError: DPI-1047: Cannot locate a 64-bit Oracle Client library: "libclntsh.so: cannot open shared object file: No such file or directory". See https://oracle.github.io/odpi/doc/installation.html#linux for help

Ian Nato
  • 963
  • 1
  • 14
  • 25

6 Answers6

27

After some more research i got the solution from Ubuntu community , after you have installed oracle instant-client you will have to integrate oracle libraries as follows:

export LD_LIBRARY_PATH=/usr/lib/oracle/<version>/client(64)/lib/${LD_LIBRARY_PATH:+:$LD_LIBRARY_PATH}

An example for 12.1 version for Linux x86_64 can be:

export LD_LIBRARY_PATH=/usr/lib/oracle/12.1/client64/lib/${LD_LIBRARY_PATH:+:$LD_LIBRARY_PATH}

where <version> indicates the version of your of your oracle instant-client e.g 11.2, 12.2
The connection parameter should be as follows

connection = cx_Oracle.connect("username/password@host/service_name e.g orcl")

to get the listener/service_name type the following in the oracle sqlplus

SQL> show parameter local_listener

literal under VALUE is your listener/service_name.

Aramis NSR
  • 1,602
  • 16
  • 26
Ian Nato
  • 963
  • 1
  • 14
  • 25
  • 4
    For installing Oracle Instant Client, I can advise to follow this [guide](https://oracle.github.io/odpi/doc/installation.html) instead (download a package and unzip it into /opt/oracle/). Also, I think it would better add [permanent paths](https://stackoverflow.com/a/26962251/9160102) – Bruno L. Feb 09 '20 at 02:51
  • Generally it's better to use `ldconfig` instead of setting `LD_LIBRARY_PATH`. This is recommended in the [Instant Client installation instructions](https://www.oracle.com/database/technologies/instant-client/linux-x86-64-downloads.html#ic_x64_inst). – Christopher Jones May 25 '21 at 22:25
  • Worked for me but instead of using the zip files, on Ubuntu I used the rpm file and then installed it using 'alien': alien -i oracle-instantclient12.1-basic-12.1.0.2.0-1.x86_64.rpm Then I had to add the library : export LD_LIBRARY_PATH=/usr/lib/oracle/21/client64/lib/${LD_LIBRARY_PATH:+:$LD_LIBRARY_PATH} – Felix Mueller Nov 25 '21 at 13:48
  • when adding the library you should not use brackets in the path at: client(64). So simple use: .../client64/... – sipi09 Dec 30 '21 at 15:00
17

I was facing the exact same problem. This is what worked for me:

  • First, I downloaded the Oracle Basic zip file. In my case, I got the 64-bit version.
  • After that, I unzipped it in an opt directory. I had to use sudo in my system
    $ sudo mkdir -p /opt/oracle  

    $ cd /opt/oracle  

    $ sudo unzip /opt/oracle/instantclient-basic-linux.x64-19.8.0.0.0dbru.zip
  • Then I installed libaio1. Note that I am working with Ubuntu
    $ sudo apt-get install libaio1
  • Finally, I added the path to the external variable LD_LIBRARY_PATH
    $ vim ~/.bashrc
  • And added this line to the .bashrc file
    export LD_LIBRARY_PATH=/opt/oracle/instantclient_19_8:$LD_LIBRARY_PATH
  • After saving the .bashrc file, I sourced it:
    $ source ~/.bashrc

Then my Python script worked nicely again.

See also the cx_oracle documentation

Michel Mesquita
  • 743
  • 7
  • 17
  • 1
    If you have no other Oracle software, (and have root access) I'd recommend using `ldconfig` (see the cx_Oracle install doc) instead of setting LD_LIBRARY_PATH. The latter may not propagate to your process in all cases, e.g through a webserver – Christopher Jones Sep 17 '20 at 12:34
  • Thanks! Great suggestion, @ChristopherJones – Michel Mesquita Sep 17 '20 at 12:44
7

For Ubuntu Linux 20.04 LTS server what worked for me (which may be obvious but wasn't to me!) is 1) when you perform the export, you need to be in the folder you intend to run the app/ command connecting to Oracle from, and although this worked, after closing the SSH terminal to the EC2 server, was then not available again which was resolved by 2) Add it to ~/.bashrc Steps in full:

With the Oracle instant client unzipped in for example: /opt/oracle/instantclient_19_9

sudo apt-get install libaio1
cd ~/your-project-folder
export LD_LIBRARY_PATH=/opt/oracle/instantclient_19_9

I then added to ~/.bashrc with:

sudo nano ~/.bashrc

And add this line:

export LD_LIBRARY_PATH=/opt/oracle/instantclient_19_9

And in the terminal run:

source ~/.bashrc

Mine worked as expected installed on an EC2 server under 'ubuntu' user with requisite nvm/ nodeJs installed

In nodeJs an example connection might look something like:

const testOracleConnection = async () => {
    let conn;

    try {
        conn = await oracledb.getConnection(oracleConfig);

        const query1 = 'select ID, anotherColumn from someTable where ID = 1111';

        const result = await conn.execute(query1);

        console.log(result);
    } catch (err) {
        console.error(err);
    } finally {
        if (conn) {
            try {
                await conn.close();
            } catch (err) {
                console.error(err);
            }
        }
    }
};
Leigh Mathieson
  • 1,658
  • 2
  • 17
  • 25
2

I have almost given up on this error. I tried potentially all the solution on the internet and nothing worked.

I was writing a similar script in Unix where I was getting the same error. I have applied the same fix that I used in shell script and it worked like charm.

The Python script worked fine accessing DB via cx_Oracle if I execute it directly. But when I schedule it through crontab I was constantly getting the error "cx_Oracle.DatabaseError: DPI-1047"

Here is the fix. Import the OS module and add the below code.

os.environ["ORACLE_HOME"] = "Your oracle lib ". In my case it is /u01/oracle/product/12.1.0.2/

dhina karan
  • 211
  • 2
  • 4
  • 2
    This is a common crontab behavior. Generally I'd recommend setting the environment variables (ORACLE_HOME, LD_LIBRARY_PATH, etc) in a shell script and then invoking Python from that script. Your crontab would invoke the shell script. Setting Oracle environment variables inside running Python programs causes problems for the unwary. Note you are using libraries from an ORACLE_HOME install, whereas the other answers on this question all use Instant Client, so they don't need the ORACLE_HOME variable set. – Christopher Jones May 25 '21 at 22:21
0

I was facing similar issue with Debian 10 image version with python, I had followed below steps to resolve my docker image issue:

Step1 : downloaded oracle-instantclient19.19-basic-19.19.0.0.0-1.x86_64.rpm from Oracle official site:

Step 2: Installed in the base image

Step 3: I had setup following ENV variables:

ENV ORACLE_HOME=/usr/lib/oracle/19.19/client64 ENV LD_LIBRARY_PATH=$ORACLE_HOME/lib

This resolved my issue

Ravikumar Kumarasamy
  • 31
  • 2
-2

If you're working with aws lambdas to connect to your RDS/OracleDB, then try this approach using Docker to automated the build for the aws lambda layer - https://medium.com/@sabithvm/building-up-on-lambda-layers-a4771d3b9c7

Wilber Hernandez
  • 1
  • 1