Connect() hangs with SSH tunnels and 11g database

[Thyrvax]

1. What versions are you using?
   Python 3.13.3 (also tried with 3.11)
   Oracledb 3.1.1 (also tried 3.0.0 and 2.5.1)
   Oracle database 11g
   Oracle instant client 18.5 (also tried with 12.2)

   platform.platform: Windows-11-10.0.26100-SP0
   sys.maxsize > 2**32: True
   platform.python_version: 3.13.3
   oracledb.version: 3.1.1

2. Is it an error or a hang or a crash? Hang

3. What error(s) or behavior you are seeing?
   While trying to connect to 11g databases through SSHtunnels, oracledb.connect() hangs and doesn't return anything.
   I tried it on multiple tunnels/databases combinaisons, while mixing oracledb / instantclient / python versions.

   If I open the tunnel through putty, oracledb.connect works.
   If I open the tunnel through another script, oracledb.connect works. (!)
   If I try to open a tunnel and connect to a 19c database, it works
   If I do the same thing using python 3.9 and cx_oracle, it works.

   If I provide a wrong username or password, I get an (expected) error.
   If I provide a wrong SID, I get an (expected) error.

   I do see an inactive connection in my v$session linked to my script.

   I tried everything I could think of and do not understand what I'm missing.

4. Does your application call init_oracle_client()? Yes, I tried with instant client versions 11.2, 12.1, 18.5
   I had success accessing a 19c db using instant client 21.18 through the same tunnel (and same script)

5. Include a runnable Python script that shows the problem.

from sshtunnel import SSHTunnelForwarder
import oracledb

# ssh config
ssh_host = "###_tunnel.com"
ssh_port = 21
ssh_username = "ssh_user"
ssh_password = "ssh_psw"

# db config
oracle_host = "ip.of.my.db"
oracle_port = 1521
oracle_sid = "DB_SID"
oracle_user = "DB_USER"
oracle_password = "DB_PSW"

#oracle_client_path = r"C:\instant_client18c\instantclient-basic-windows.x64-18.5.0.0.0dbru\instantclient_18_5"
oracle_client_path = r"C:\instant_client11g\instantclient-basic-windows.x64-11.2.0.4.0\instantclient_11_2"
oracledb.init_oracle_client(lib_dir=oracle_client_path)
print("Thin mode enabled?", oracledb.is_thin_mode())

try:
    with SSHTunnelForwarder(
            (ssh_host, ssh_port),
            ssh_username=ssh_username,
            ssh_password=ssh_password,
            remote_bind_address=(oracle_host, oracle_port),
    ) as tunnel:
        print(f"binding on {tunnel.local_bind_host}:{tunnel.local_bind_port}")
        print(oracledb.clientversion())
        param = oracledb.ConnectParams(
            user=oracle_user,
            password=oracle_password,
            host="localhost",
            port=tunnel.local_bind_port,
            sid=oracle_sid,
        )
        with oracledb.connect(params=param) as connection:
            print("connected to Oracle 11.2!") # this never prints for tunnel + 11g
            with connection.cursor() as cursor:
                cursor.execute("SELECT 'query success' AS TEST FROM dual")
                result = cursor.fetchone()
                print(result[0])

except Exception as e:
    print(f"Error : {e}")

[cjbj]

If I correctly read the extensive list in point 3 of what you have tried, you can get cx_Oracle to connect to your 11g database over the tunnel:

While trying to connect to 11g databases .... If I do the same thing using python 3.9 and cx_oracle, it works.

If so, make sure the cx_Oracle script calls cx_Oracle.init_oracle_client(lib_dir=oracle_client_path) and can connect. Then change the script in one way only: to use import oracledb as cx_Oracle. Run it in the same terminal & ssh session that cx_Oracle was able to connect in.

In Thick mode, connectivity is handled by the Oracle Client library which is not part of the python-oracledb project, so if it works in cx_Oracle it should (^TM) work in python-oracledb Thick mode.

[Thyrvax]

I just did the test on my 3.9 venv.
I do confirm that the connection to 11g through tunnel using cx_oracle is working

Then I tried with import oracledb as cx_Oracle, and it fails in the same way, hanging when trying to acquire connection.

I do have a workaround, using threading to start the tunnel in another thread works.

This bugs me because I spent too much time trying to make it works, but I also understand that it may be a weird edge case on an EoL version which may not be worth your time. I'm still up for trying thing, but won't blame you if you don't

[anthony-tuininga]

Did you also include the call to init_oracle_client()? That's important in order to enable thick mode.

[Thyrvax]

Yes, it was already a requirement when using cx_oracle, so it was part of the script. This is exactly what I used :

from sshtunnel import SSHTunnelForwarder
#import cx_Oracle  #--> this works
import oracledb as cx_Oracle #-- this does not work

# ssh config
ssh_host = "###_tunnel.com"
ssh_port = 21
ssh_username = "ssh_user"
ssh_password = "ssh_psw"

# db config
oracle_host = "ip.of.my.db"
oracle_port = 1521
oracle_sid = "DB_SID"
oracle_user = "DB_USER"
oracle_password = "DB_PSW"

oracle_client_path = r"C:\instant_client18c\instantclient-basic-windows.x64-18.5.0.0.0dbru\instantclient_18_5"
cx_Oracle.init_oracle_client(lib_dir=oracle_client_path)
try:

    with SSHTunnelForwarder(
            (ssh_host, ssh_port),
            ssh_username=ssh_username,
            ssh_password=ssh_password,
            remote_bind_address=(oracle_host, oracle_port),
    ) as tunnel:
        local_port = tunnel.local_bind_port
        print(f"tunnel connected to port {tunnel.local_bind_port}")

        # Connexion à la base Oracle via le tunnel
        dsn = f"127.0.0.1:{tunnel.local_bind_port}/{oracle_sid}"
        print(f"DSN used : {dsn}")

        with cx_Oracle.connect(
                user=oracle_user,
                password=oracle_password,
                dsn=dsn
        ) as connection:
            print("Connected to Oracle 11.2!")

            with connection.cursor() as cursor:
                cursor.execute("SELECT 'Connected !!' AS TEST FROM dual")
                result = cursor.fetchone()
                print(result[0])

except Exception as e:
    print(f"Error : {e}")

I previously incorrectly stated that it worked using threading. It does not.
The only case I got working was when using a subprocess to make the database call.
Opening the tunnel in a subprocess and then accessing the database from the main script does not work.

edit: I configured a WSL Ubuntu, then used the same script with python 3.12 and instantclient 12_2, and got the same result