I am trying to connect to an Oracle DB using the oracledb connection library and when I try it It simply hangs on with oracledb.connect even when following the documentation.

Here is the sample code. I would expect it to output the system time

#!/usr/bin/env python3
import os
import oracledb

un = os.environ["USER"]
pw = os.environ["PASSWORD"]
tns_entry = os.environ["TNS_ENTRY"]
config_dir = f'{os.environ["PWD"]}/wallets/test/'

print("USERNAME:", un)
print("PASSWORD:", pw)
print("TNS_ENTRY:", tns_entry)
print("TNS_ADMIN:", config_dir)

with open(config_dir+"/tnsnames.ora","r") as f:
    print(f.read())

with open(config_dir+"/sqlnet.ora","r") as f:
    print(f.read())

with oracledb.connect(
    user=un,
    password=pw,
    dsn=tns_entry,
    config_dir=config_dir,
    wallet_location=config_dir
) as connection:
    with connection.cursor() as cursor:
        sql = "select systimestamp from dual"
        for (r,) in cursor.execute(sql):
            print(r)

Here is the output:

-> % ./dbtest.py                         
USERNAME: sreapp
PASSWORD: ***
TNS_ENTRY: medium
TNS_ADMIN: /Users/cbongior/dev/oracle/fleetman/wallets/test/
medium = (description= (retry_count=2)(retry_delay=3)(address=(https_proxy=100.92.6.113)(https_proxy_port=3128)(protocol=tcps)(port=1522)(host=zxqgy1nz.adb.us-ashburn-1.oraclecloud))(connect_data=(service_name=gea38c0017d364a_sreadwt1_medium.adb.oraclecloud))(security=(ssl_server_dn_match=no)))
SQLNET.USE_HTTPS_PROXY=on
WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="$TNS_ADMIN")))
SSL_SERVER_DN_MATCH=no

and in the console, effectively the same steps (confirming setting are valid):

-> % echo $USER
sreapp
(.venv) cbongior@cbongior-mac [16:21:49] [~/dev/oracle/fleetman] [main *]
-> % echo $PASSWORD
****
(.venv) cbongior@cbongior-mac [16:21:54] [~/dev/oracle/fleetman] [main *]
-> % echo $TNS_ENTRY
medium
(.venv) cbongior@cbongior-mac [16:22:05] [~/dev/oracle/fleetman] [main *]
-> % echo $TNS_ADMIN
/Users/cbongior/dev/oracle/fleetman/wallets/test
(.venv) cbongior@cbongior-mac [16:22:21] [~/dev/oracle/fleetman] [main *]
-> % echo $TNS_ADMIN
(.venv) cbongior@cbongior-mac [16:22:32] [~/dev/oracle/fleetman] [main *]
-> % cat $TNS_ADMIN/tnsnames.ora
medium = (description= (retry_count=20)(retry_delay=3)(address=(https_proxy=100.92.6.113)(https_proxy_port=3128)(protocol=tcps)(port=1522)(host=zxqgy1nz.adb.us-ashburn-1.oraclecloud))(connect_data=(service_name=gea38c0017d364a_sreadwt1_medium.adb.oraclecloud))(security=(ssl_server_dn_match=no)))
[main *]
-> % cat $TNS_ADMIN/sqlnet.ora
SQLNET.USE_HTTPS_PROXY=on
WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="$TNS_ADMIN")))
SSL_SERVER_DN_MATCH=no%    



(.venv) cbongior@cbongior-mac [16:22:40] [~/dev/oracle/fleetman] [main *]
-> % sqlplus "${USER}/${PASSWORD}@${TNS_ENTRY}" <<< "select systimestamp from dual; 
exit;"


SQL*Plus: Release 19.0.0.0.0 - Production on Tue Mar 25 16:21:01 2025
Version 19.8.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.

Last Successful login time: Tue Mar 25 2025 16:19:30 -07:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.27.0.1.0

SQL> 
SYSTIMESTAMP
---------------------------------------------------------------------------
25-MAR-25 11.21.04.273828 PM +00:00

SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.27.0.1.0
(.venv) cbongior@cbongior-mac [16:21:05] [~/dev/oracle/fleetman] [main *]

When I move those code to a host behind our firewall (removing the proxy settings), it works immediately:

|main ↑7 S:4 U:3 ?:6 ✗| → dbtest.py 
/home/cbongior/.local/lib/python3.6/site-packages/oracledb/__init__.py:39: UserWarning: Python 3.6 is no longer supported by the Python core team. Therefore, support for it is deprecated in python-oracledb and will be removed in a future release
  warnings.warn(message)
USERNAME: sreapp
PASSWORD: ****
TNS_ENTRY: medium
TNS_ADMIN: /home/cbongior/dev/oracle/fleetman/wallets/test/
medium = (description= (retry_count=2)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=zxqgy1nz.adb.us-ashburn-1.oraclecloud))(connect_data=(service_name=gea38c0017d364a_sreadwt1_medium.adb.oraclecloud))(security=(ssl_server_dn_match=no)))

SQLNET.USE_HTTPS_PROXY=off
WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="$TNS_ADMIN")))
SSL_SERVER_DN_MATCH=no

2025-03-27 00:13:06.250068

It's somehow related to the proxy settings in the tnsnames.ora. I have explicitly set the proxy settings in the connect string, to no luck

I am trying to connect to an Oracle DB using the oracledb connection library and when I try it It simply hangs on with oracledb.connect even when following the documentation.

Here is the sample code. I would expect it to output the system time

#!/usr/bin/env python3
import os
import oracledb

un = os.environ["USER"]
pw = os.environ["PASSWORD"]
tns_entry = os.environ["TNS_ENTRY"]
config_dir = f'{os.environ["PWD"]}/wallets/test/'

print("USERNAME:", un)
print("PASSWORD:", pw)
print("TNS_ENTRY:", tns_entry)
print("TNS_ADMIN:", config_dir)

with open(config_dir+"/tnsnames.ora","r") as f:
    print(f.read())

with open(config_dir+"/sqlnet.ora","r") as f:
    print(f.read())

with oracledb.connect(
    user=un,
    password=pw,
    dsn=tns_entry,
    config_dir=config_dir,
    wallet_location=config_dir
) as connection:
    with connection.cursor() as cursor:
        sql = "select systimestamp from dual"
        for (r,) in cursor.execute(sql):
            print(r)

Here is the output:

-> % ./dbtest.py                         
USERNAME: sreapp
PASSWORD: ***
TNS_ENTRY: medium
TNS_ADMIN: /Users/cbongior/dev/oracle/fleetman/wallets/test/
medium = (description= (retry_count=2)(retry_delay=3)(address=(https_proxy=100.92.6.113)(https_proxy_port=3128)(protocol=tcps)(port=1522)(host=zxqgy1nz.adb.us-ashburn-1.oraclecloud))(connect_data=(service_name=gea38c0017d364a_sreadwt1_medium.adb.oraclecloud))(security=(ssl_server_dn_match=no)))
SQLNET.USE_HTTPS_PROXY=on
WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="$TNS_ADMIN")))
SSL_SERVER_DN_MATCH=no

and in the console, effectively the same steps (confirming setting are valid):

-> % echo $USER
sreapp
(.venv) cbongior@cbongior-mac [16:21:49] [~/dev/oracle/fleetman] [main *]
-> % echo $PASSWORD
****
(.venv) cbongior@cbongior-mac [16:21:54] [~/dev/oracle/fleetman] [main *]
-> % echo $TNS_ENTRY
medium
(.venv) cbongior@cbongior-mac [16:22:05] [~/dev/oracle/fleetman] [main *]
-> % echo $TNS_ADMIN
/Users/cbongior/dev/oracle/fleetman/wallets/test
(.venv) cbongior@cbongior-mac [16:22:21] [~/dev/oracle/fleetman] [main *]
-> % echo $TNS_ADMIN
(.venv) cbongior@cbongior-mac [16:22:32] [~/dev/oracle/fleetman] [main *]
-> % cat $TNS_ADMIN/tnsnames.ora
medium = (description= (retry_count=20)(retry_delay=3)(address=(https_proxy=100.92.6.113)(https_proxy_port=3128)(protocol=tcps)(port=1522)(host=zxqgy1nz.adb.us-ashburn-1.oraclecloud))(connect_data=(service_name=gea38c0017d364a_sreadwt1_medium.adb.oraclecloud))(security=(ssl_server_dn_match=no)))
[main *]
-> % cat $TNS_ADMIN/sqlnet.ora
SQLNET.USE_HTTPS_PROXY=on
WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="$TNS_ADMIN")))
SSL_SERVER_DN_MATCH=no%    



(.venv) cbongior@cbongior-mac [16:22:40] [~/dev/oracle/fleetman] [main *]
-> % sqlplus "${USER}/${PASSWORD}@${TNS_ENTRY}" <<< "select systimestamp from dual; 
exit;"


SQL*Plus: Release 19.0.0.0.0 - Production on Tue Mar 25 16:21:01 2025
Version 19.8.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.

Last Successful login time: Tue Mar 25 2025 16:19:30 -07:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.27.0.1.0

SQL> 
SYSTIMESTAMP
---------------------------------------------------------------------------
25-MAR-25 11.21.04.273828 PM +00:00

SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.27.0.1.0
(.venv) cbongior@cbongior-mac [16:21:05] [~/dev/oracle/fleetman] [main *]

When I move those code to a host behind our firewall (removing the proxy settings), it works immediately:

|main ↑7 S:4 U:3 ?:6 ✗| → dbtest.py 
/home/cbongior/.local/lib/python3.6/site-packages/oracledb/__init__.py:39: UserWarning: Python 3.6 is no longer supported by the Python core team. Therefore, support for it is deprecated in python-oracledb and will be removed in a future release
  warnings.warn(message)
USERNAME: sreapp
PASSWORD: ****
TNS_ENTRY: medium
TNS_ADMIN: /home/cbongior/dev/oracle/fleetman/wallets/test/
medium = (description= (retry_count=2)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=zxqgy1nz.adb.us-ashburn-1.oraclecloud))(connect_data=(service_name=gea38c0017d364a_sreadwt1_medium.adb.oraclecloud))(security=(ssl_server_dn_match=no)))

SQLNET.USE_HTTPS_PROXY=off
WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="$TNS_ADMIN")))
SSL_SERVER_DN_MATCH=no

2025-03-27 00:13:06.250068

It's somehow related to the proxy settings in the tnsnames.ora. I have explicitly set the proxy settings in the connect string, to no luck

• python
• python-oracledb

• For various reasons, the connect() arguments need to be different for Thin mode than Thick. With mTLS connections, you need to pass wallet_location and wallet_password parameters in Thin mode since it can only use the PEM file. See python-oracledb.readthedocs.io/en/latest/user_guide/…. If this isn't your scenario, then update your question with more information about the DB configuration. (Also, don't use http_proxy for any production usage - it's too slow) – Christopher Jones Commented Mar 26 at 22:14
• I've updated it with another debug case tested. It's absolutely related to this proxy setting in the tnsnames. – Christian Bongiorno Commented Mar 27 at 0:05
• I am not sure if this is related to mTLS in anyway, I just know that, from my laptop I need the proxy and the quick client works and the python code doesn't. I'll see about creating a whole new question since it's very much a proxy issue – Christian Bongiorno Commented Mar 27 at 0:15
• Sidenote, retry_count for some reason is different in the console (20 vs 2). – Man made of meat Commented Mar 27 at 2:59
• Follow the doc and use the appropriate parameters for your DB (mTLS or 1-way TLS) and proxy. Thin mode works for me. I'm happy to talk more, if needed - drop me a line. – Christopher Jones Commented Mar 27 at 5:17

1 Answer 1

On investigation, the problem was python-oracledb 3.0.0's changed network stack not handling proxies correctly. Solutions include using python-oracledb 2.5.1 until the regression is patched, or using the data service IP address instead of the hostname.