Connecting to Oracle DB from WSL through ODBC/RStudio Pro Drivers from Python

Posit Professional & Hosted Products Posit Connect
,
1

Hey everyone,

I currently would like to connect to our Oracle DB from Python using the Windows Subsystem for Linux for some python development. We've set up the DSN-Connection on the RSConnect machine and I'd like to use a connection method, that will work locally (WSL, Ubuntu 18) and remotely (on the Connect Server) and we currently try to use pyodbc instead of cx_Oracle.

I stayed close to the installation steps described here: Redirecting...

  • install oracle-instantclient19.11-basic-19.11.0.0.0-1.x86_64.rpm (transformed toto *.deb file via alien), which installs into /opt/oracle
  • install rstudio drivers
  • create symbolic link from instantclient programs to oracle driver directory
  • populate /etc/odbc.ini

This is the test code, I'm running

import pyodbc

oracle_con = pyodbc.connect(
    user = "<user>",
    pwd="<pwd>"",
    DSN=DWH_PS",
    autocommit=True
)

And this is the error I currently receive.

Oracle Caller Interface: ORA-12541: TNS:no listener\n (3000) (SQLDriverConnect)'

When I run

ldd /opt/rstudio-drivers/oracle/bin/lib/liboracleodbc_sb64.so

I get

ldd /opt/rstudio-drivers/oracle/bin/lib/liboracleodbc_sb64.so
linux-vdso.so.1 (0x00007fffc3a29000)
libdl.so.2 => /lib/x86_64-linux-gnu/libdl.so.2 (0x00007ffbe8a00000)
libpthread.so.0 => /lib/x86_64-linux-gnu/libpthread.so.0 (0x00007ffbe89dd000)
libocci.so.19.1 => /opt/oracle/instantclient_19_11/libocci.so.19.1 (0x00007ffbe86f0000)
libclntsh.so.19.1 => /opt/oracle/instantclient_19_11/libclntsh.so.19.1 (0x00007ffbe45a9000)
librt.so.1 => /lib/x86_64-linux-gnu/librt.so.1 (0x00007ffbe4590000)
libstdc++.so.6 => /lib/x86_64-linux-gnu/libstdc++.so.6 (0x00007ffbe43a0000)
libm.so.6 => /lib/x86_64-linux-gnu/libm.so.6 (0x00007ffbe4251000)
libgcc_s.so.1 => /lib/x86_64-linux-gnu/libgcc_s.so.1 (0x00007ffbe4230000)
libc.so.6 => /lib/x86_64-linux-gnu/libc.so.6 (0x00007ffbe4030000)
/lib64/ld-linux-x86-64.so.2 (0x00007ffbeb6e8000)
libnnz19.so => /opt/oracle/instantclient_19_11/libnnz19.so (0x00007ffbe3880000)
libnsl.so.1 => /lib/x86_64-linux-gnu/libnsl.so.1 (0x00007ffbe3860000)
libaio.so.1 => /lib/x86_64-linux-gnu/libaio.so.1 (0x00007ffbe45a0000)
libresolv.so.2 => /lib/x86_64-linux-gnu/libresolv.so.2 (0x00007ffbe3830000)
libclntshcore.so.19.1 => /opt/oracle/instantclient_19_11/libclntshcore.so.19.1 (0x00007ffbe328c000)

which looks fine.

A colleague has got a similar setup to work without the pro drivers and using the zip-version of the instantclient + the cx_Oracle package instead.

I've tried copying a listener.ora (which I have on my Windows System), but I'm not sure, what the appropriate location is and how to update the oracle services to a new config.

I'm generally unsure, what the problem is. :slight_smile: