Thin via SCAN listener fails with DPY-6000 -- SERVICE_NAME not passed to local listener · Issue #39 · oracle/python-oracledb (original) (raw)

  1. What versions are you using?

listeners: 18, database: 18
platform.platform: Windows-10-10... (We see the same error from Lunix clients)
sys.maxsize > 2**32: True
platform.python_version: 3.10.5
oracledb.version: 1.0.2

  1. Is it an error or a hang or a crash?

error

  1. What error(s) or behavior you are seeing?

if we replace the SCAN address with the local listener address, the connection functions without error.

Traceback (most recent call last):
  File "C:\apps\tmp\test.py", line 38, in <module>
    with oracledb.connect(user=un, password=pw, dsn=cs) as connection:
  File "C:\apps\Python\Python310\lib\site-packages\oracledb\connection.py", line 1000, in connect
    return conn_class(dsn=dsn, pool=pool, params=params, **kwargs)
  File "C:\apps\Python\Python310\lib\site-packages\oracledb\connection.py", line 128, in __init__
    impl.connect(params_impl)
  File "src\oracledb\impl/thin/connection.pyx", line 347, in oracledb.thin_impl.ThinConnImpl.connect
  File "src\oracledb\impl/thin/connection.pyx", line 163, in oracledb.thin_impl.ThinConnImpl._connect_with_params
  File "src\oracledb\impl/thin/connection.pyx", line 129, in oracledb.thin_impl.ThinConnImpl._connect_with_description
  File "src\oracledb\impl/thin/protocol.pyx", line 241, in oracledb.thin_impl.Protocol._process_message
  File "src\oracledb\impl/thin/connection.pyx", line 234, in oracledb.thin_impl.ThinConnImpl._connect_with_address
  File "src\oracledb\impl/thin/protocol.pyx", line 150, in oracledb.thin_impl.Protocol._connect_phase_one
  File "src\oracledb\impl/thin/protocol.pyx", line 262, in oracledb.thin_impl.Protocol._process_message
  File "src\oracledb\impl/thin/protocol.pyx", line 241, in oracledb.thin_impl.Protocol._process_message
  File "src\oracledb\impl/thin/messages.pyx", line 1578, in oracledb.thin_impl.ConnectMessage.process
  File "C:\apps\Python\Python310\lib\site-packages\oracledb\errors.py", line 103, in _raise_err
    raise exc_type(_Error(message)) from cause
oracledb.exceptions.OperationalError: DPY-6000: cannot connect to database. Listener refused connection. (Similar to ORA-12504)

the local listener logs the following:

22-JUL-2022`` 10:53:48 * (CONNECT_DATA=(SERVICE_NAME=)(SERVER=dedicated)(CID=(PROGRAM=C:\apps\Python\Python310\python.exe)(HOST=___)(USER=___))) * establish * 12504
TNS-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA
 TNS-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA
  1. Does your application call init_oracle_client()?

no, we wish to use thin.

  1. Include a runnable Python script that shows the problem.
# test.py
import oracledb
import os

oracledb.defaults.config_dir = "C:/apps"
un = os.environ.get('USER_NAME')
pw = os.environ.get('PASSWORD')
cs = os.environ.get('CONNECT_STRING')

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

ConnectParams(user='', proxy_user=None, host='host..org', port=1521, protocol='tcp', https_proxy=None, https_proxy_port=0, service_name='mydb.___.org', sid=None, server_type=None, cclass=None, purity=0, expire_time=0, retry_count=0, retry_delay=0, tcp_connect_timeout=60.0, ssl_server_dn_match=True, ssl_server_cert_dn=None, wallet_location=None, events=False, mode=0, disable_oob=False, stmtcachesize=20, edition=None, tag=None, matchanytag=False, config_dir='C:\apps\', appcontext=None, shardingkey=None, supershardingkey=None, debug_jdwp=None)

have tested many connection options -- all end with the DPY-6000 unless a local listener is used.
our SCAN listener configuration may be at fault here, but we have other thin connections connecting via our SCAN listeners without problems.