oracle
Add support for IFILE in tnsnames.ora
- What versions are you using?
SQL> SELECT BANNER FROM v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
import oracledb
print("oracledb.__version__:", oracledb.__version__)
oracledb.__version__: 2.1.0
>>> import sys
>>> import platform
>>> print("platform.platform:", platform.platform())
platform.platform: Linux-5.10.0-22-amd64-x86_64-with-glibc2.31
>>> print("sys.maxsize > 2**32:", sys.maxsize > 2**32)
sys.maxsize > 2**32: True
>>> print("platform.python_version:", platform.python_version())
platform.python_version: 3.11.8
- Is it an error or a hang or a crash?
a crash
- What error(s) or behavior you are seeing?
DatabaseError: (oracledb.exceptions.DatabaseError) DPY-4000: unable to find "XXXX.WORLD" in /opt/oracle/instantclient_19_16/network/admin/tnsnames.ora
- Does your application call init_oracle_client()?
No, running Thin mode
- Include a runnable Python script that shows the problem.
import oracledb
with oracledb.connect(user="myuser", password="secretpassword", dsn="XXXX.WORLD") as connection:
with connection.cursor() as cursor:
sql = """select sysdate from dual"""
for r in cursor.execute(sql):
print(r)
- crashes if the tnsnames.ora file contains FILE=
- works if all definitions are included directly in tnsnames.ora file
Can you provide an example? Or a reference to the Oracle documentation that shows how this is used? A quick search doesn't bring up anything relevant. Thanks!
Like so:
https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/IFILE.html#GUID-F8AC6FC6-F456-481F-8997-3B0E906BB745
thanks to you! Our DBA team is looking to extend the usage of IFILE to our deployed tnsnames.ora files. Do think that is something that can be added to the roadmap soon?
I am looking into it and I hope to include it in version 2.2. I don't know the ETA for that exactly yet, but the patch will be available before the release if you are able to build binaries yourself?
Yes, it is. I hope to have a patch available fairly soon. Stay tuned!
A patch is now available. If you are able to build from source you can verify that it works for you, too.
hi Anthony, I've tried the patch but getting below error:
oracledb.exceptions.DatabaseError: DPY-4031: connect string for network service name 'XXXX.WORLD' found in file 'Y:\Oracle\Network\Admin\tnsnames.ora' differs from the same entry in 'Y:\Oracle\Network\Admin\tnsnames.ora'
I've checked with our DBA team and it's allowed to have multiple service name entries with the same name. The parser keeps the last entry it finds (though I've not yet found any mention of this in the documentation).
Yes, there are some differences with how this has been implemented compared to the C and Java implementations, and yes, this is not well-documented, if at all!
- network service names cannot be defined differently (why would you want that anyway???); if they have the same value such duplicate definitions are ignored (C/Java simply ignore duplicates and the last value read is retained)
- no limit on the number of levels deep you can go (C/Java have a limit on how many levels deep you can go)
- cycles are detected and an error raised (C/Java simply read files as directed until a number of levels have been reached)
- IFILE directives that reference missing or unreadable files raise an exception (C/Java ignore these)
In each of the above cases I believe the Python implementation is an improvement and results in less confusion over how network service names are resolved and what value they resolve to! With respect to the particular error you are getting, can your DBA team explain WHY they want to have the same network service name defined differently in the same file? And why that is not a source of confusion?
I understand this could be seen as an improvement however the problem is that connecting with sqlplus or with python-oracledb have different behaviors whereas we would expect consistency between implementations, no matter how we connect.
Checking if I can get more information from our DBA team. Initial feedback is that it's been working like so for years and I don't expect any changes since it works for all other use cases.
You can have consistency -- once you correct your configuration to remove the ambiguity! :-) Each of the differences noted above are for arguably invalid configurations or due to limitations of the C and Java implementations. If you have any good counter arguments I would love to hear them!
Do you agree this is a breaking change though? I've tested with a tnsnames.ora file that does not contain the IFILE directive.
- in 2.1.2, it works ok
- in this version, it no longer works
Technically, I suppose. It can also be considered a bug fix. :-) I'll add a release note for that scenario. Thanks for pointing it out!
Following up with our DB team. The duplicate entry logic is used as a feature using IFILE where the last entry is the one used to allow for overrides.
e.g. tnsnames.ora IFILE=ww.ora # default IP if there is a direct Network access for a database IFILE=local.ora # store local connection only ( in same subnetwork in order to prevent using CMAN / or network timeout )
where both files can define the same entry but the local one can redefine an entry based on network access.
Also, others appear to be using this logic as well (though not documented officially).
- https://forums.oracle.com/ords/apexds/post/tnsnames-file-with-duplicate-entries-8146
- https://bijoos.com/oraclenotes/2015/1509/
In summary, the current implementation is going to break our connections including with tnsnames.ora that do not use IFILE. Clearly not seeing this as a bug fix but as a regression ;) Could you please consider removing this check?
Human ingenuity never ceases to amaze me! I will discuss it internally but I understand your point and can see the use of such a "feature". :-)