How to connect two Oracle Autonomous Databases using cx_Oracle wallet in one python program

[Praful-Pasarkar]

1. What versions are you using?
   Oracle Autonomous Databases in OCI - Transaction database and DW database.

platform.platform: Windows-10-10.0.19041-SP0
sys.maxsize > 2**32: True
platform.python_version: 3.8.4rc1


cx_Oracle.version: 8.0.0
cx_Oracle.clientversion: (19, 6, 0, 0, 0)

2. Describe the problem

I have Oracle Transaction (TRX) and Data warehouse (DW) databases in OCI. I wrote python program to connect to the databases using cx_Oracle wallet. I can connect to these database in separate python program without any issues.

Now, I have to connect to both TRX and DW databases in one python program. and this is not working!!!

This is what i have already done:

1. installed Oracle Client 19.6 in my C:\instantclient_19_6
2. Download both wallets and saved in C:\instantclient_19_6\network\admin_trx and C:\instantclient_19_6\network\admin_dw
3. I copied three files from cwallet.sso, sqlnet.ora and tnsnames.ora in their respective folders.
4. Updated the sqlnet.ora file with correct directory path (as mentioned above)

When i run my python file with both "def" together as seen in the code, I get

(datetime.datetime(2021, 4, 9, 11, 23, 22),)

...

connection1 = cx_Oracle.connect('username', 'passwd', 'devtrxdb_low')

cx_Oracle.DatabaseError: ORA-12154: TNS:could not resolve the connect identifier specified

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

my code is here:
https://github.com/Praful-Pasarkar/python_OracleDB/blob/main/call_both_db.py
OR copied code here.

import cx_Oracle
import os

def dwdb():
    os.environ['TNS_ADMIN']=r"C:\instantclient_19_6\network\admin_dw"
    connection1 = cx_Oracle.connect('username', 'password', 'devdwdb_low')
    mysql1= """select sysdate from dual"""
    cursor = connection1.cursor()
    cursor.execute(mysql1)
    result = cursor.fetchone()
    print(result)
    connection1.close()

def trxdb():
    os.environ['TNS_ADMIN']=r"C:\instantclient_19_6\network\admin_trx"
    connection2 = cx_Oracle.connect('username', 'password', 'devtrxdb_low')
    mysql1= """select sysdate from dual"""
    cursor = connection2.cursor()
    cursor.execute(mysql1)
    result = cursor.fetchone()
    print(result)
    connection2.close()

dwdb()
trxdb()

[anthony-tuininga]

The problem you are experiencing is that the environment variable TNS_ADMIN is read only the first time that a connection is established and used throughout the lifetime of the program. There is a solution, however. You can find that written in this comment.

[cjbj]

Use 21c client libraries - I'm not convinced all the important enhancements to support this nicely have landed in 19.

At the least, you should get a much newer 19c release update. 19.10 is already out.

[Praful-Pasarkar]

The problem you are experiencing is that the environment variable TNS_ADMIN is read only the first time that a connection is established and used throughout the lifetime of the program. There is a solution, however. You can find that written in this comment.

thanks @anthony-tuininga this helped me with 19.6 instant client. This has helped me to overcome the current situation.

[Praful-Pasarkar]

Use 21c client libraries - I'm not convinced all the important enhancements to support this nicely have landed in 19.

At the least, you should get a much newer 19c release update. 19.10 is already out.

Thanks @cjbj I had already 19.6 and I didn't see a need to move to 19.10 earlier. May be this is time to change the version.

[stale]

This issue has been automatically marked as inactive because it has not been updated recently. It will be closed if no further activity occurs. Thank you for your contributions.

[stale]

This issue has been automatically closed because it has not been updated for a month.

[cjbj]

A followup:

I said:

Use 21c client libraries - I'm not convinced all the important enhancements to support this nicely have landed in 19.

These important fixes have been backported to the 19c Oracle client libraries in version 19.17.