DPY-4000 when using EZCONNECT syntax (which worked with 1.2.1)

[hvbtup]

1. What versions are you using?

DB: Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production

Also run Python and show the output of ...:

platform.platform: Windows-2012ServerR2-6.3.9600-SP0 sys.maxsize > 2**32: True platform.python_version: 3.10.7

oracledb.version: 2.2.1

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

Crash

While this worked with oracledb-1.2.1. I only switched to 2.2.1 because I saw the exception from https://github.com/oracle/python-oracledb/issues/30 in a call to executemany with 1.2.1.

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

... File "E:...\ROG\import_service\python\import_service.py", line 144, in connect self._conn = oracledb.connect(self.connect_string) File "E:...import_service\3rdParty\python-3.10\lib\site-packages\oracledb\connection.py", line 1158, in connect return conn_class(dsn=dsn, pool=pool, params=params, **kwargs) File "E:...\import_service\3rdParty\python-3.10\lib\site-packages\oracledb\connection.py", line 517, in init dsn = params_impl.process_args(dsn, kwargs, thin) File "src\oracledb\impl/base/connect_params.pyx", line 623, in oracledb.base_impl.ConnectParamsImpl.process_args File "src\oracledb\impl/base/connect_params.pyx", line 560, in oracledb.base_impl.ConnectParamsImpl.parse_connect_string File "src\oracledb\impl/base/connect_params.pyx", line 558, in oracledb.base_impl.ConnectParamsImpl.parse_connect_string File "src\oracledb\impl/base/connect_params.pyx", line 338, in oracledb.base_impl.ConnectParamsImpl._parse_connect_string File "E:...\import_service\3rdParty\python-3.10\lib\site-packages\oracledb\errors.py", line 182, in _raise_err raise error.exc_type(error) from cause oracledb.exceptions.DatabaseError: DPY-4000: unable to find "//rocket:1521/E122" in E:...\import_service\conf\tnsnames.ora

The application uses a string of the following form as self.connect_string:

username/password@//rocket:1521/E122

Username and password contain only ASCII letters.

6. Does your application call init_oracle_client()?

No (Thin mode).

This is probably important:

The program just calls oracledb.connect(x) with a single string x of the form mentioned above.

The environment variable TNS_ADMIN is set and points to a directory with sqlnet.ora and tnsnames.ora. I know that these files are not necessary when using EZCONNECT syntax, but the program is written generic (also supports Thick Mode and TNS aliases).

Contents of tnsnames.ora:

TEST.world =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oracle-db.local)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TEST)
    )
  )

Contents of sqlnet.ora:

NAMES.DIRECTORY_PATH= (TNSNAMES,EZCONNECT)

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

Format code by using three backticks on a line before and after code snippets, for example:

import oracledb
import os
os.environ["TNS_ADMIN"] = r"E:\...\import_service\conf"
oracledb.connect("username/password@//rocket:1521/E122")

[hvbtup]

Of course you have to adapt the TNS_ADMIN path accordingly in order to test.

[cjbj]

This is a 'known feature' - I don't even remember it working at all, but guess it must have. Just this week we were talking about it, and the need for a better parser.

The solution is to remove the nominally-optional "//". Try oracledb.connect("username/password@rocket:1521/E122")

[anthony-tuininga]

It wouldn't have worked in thin mode at any time, but it would have (and still does) work in thick mode. The main reason it wouldn't have worked in thin mode is because I didn't even know about it until very recently. :-) As Chris mentioned, remove the "//" which shouldn't be there. Or add the protocol ("tcp:") before the "//". As Chris mentioned a better parser is being considered, but this is the least of the issues that needs to be resolved!

[cjbj]

Well, considering I used to favor & document the use of the optional '//', and it's part of the official syntax, I wouldn't go so far as to say it shouldn't be there!

[hvbtup]

@anthony-tuininga I can confirm that the EZCONNECT syntax definitely worked in thin mode in 1.2.1.

[anthony-tuininga]

I presume that removeing the // resolves the issue for you? It is possible that it stopped working when the improved regex was introduced (to resolve a different issue). You're the first to note the failure with the // (degenerate form of the protocol designation). In any case, I will look at adding the improved parser that was discussed.

[hvbtup]

Last week, I finally found an official syntax description (for the new "Easy Connect Plus" syntax, however) at https://download.oracle.com/ocomdocs/global/Oracle-Net-Easy-Connect-Plus.pdf

According to this document, //server:port/service_name is invalid. It should be either tcp://.... or without //.

BTW: Anthony, since you are working for Oracle now: My co-workers tell me that they really would appreciate if all those Oracle client drivers would behave the same regarding SQL*Net (for example, use the same environment variables like TNS_ADMIN, consider tnsnames.ora AND sqlnet.ora, etc).

I will test later today if removing // resolves the issue...

[cjbj]

That tech brief (which I originally released for the Net team, but is now coordinated by @sharadraju) shows the syntax as [[protocol:]//]... which means it doesn't need a protocol, and can optionally start with "//".

The actual official syntax varies with which version of the Thick client is in use - and which of those variants is implemented in the relevant Thin client! For example the 23ai Thick client syntax is documented in the Database Net Services Administrator's Guide. (BTW, this is linked to from the python-oracledb doc on the Easy Connect syntax).

Regarding the use of '//', @anthony-tuininga is finalizing a new connection string parser, so I will hold him to supporting an optional '//'.

Regarding your final request, the overall direction is for standardization across drivers. Now that JDBC is under the same management line, there is considerable coordination across all Oracle drivers. Unfortunately we can't dramatically rationalize and simplify configuration files syntaxes or search paths, due to their long history and widespread use.

[hvbtup]

I presume that removeing the // resolves the issue for you?

Yes, using just username/password@server:port/service_name resolves the issue.

[anthony-tuininga]

I have pushed a patch that corrects this issue. If you are able to build from source you can verify that it works for you, too.

[hvbtup]

Sorry, I can't build from source. Guess I'll just have to wait for the next release.

[anthony-tuininga]

This was included in python-oracledb 2.4.0 which was just released.