I'm struggling to connect a Python script with two different Oracle databases at the same time.

[analisis1data]

Hello,

I have to execute a Python script. In this script, I import two modules (both using oracledb). In one of them, I create a connection to ADW using Thin mode. In the other, it creates a connection to an Oracle database 11g using Thick mode.

When I execute the main script, the module that connects to ADW says "Oracle Client library has already been initialized." I know this occurs because they are sharing an oracledb state.

I tried to create a copy of oracledb files with another name so that they use virtually different libraries, but it failed.

Notes:
For thick mode I have to execute oracledb.init_oracle_client(lib_dir="....instantclient_21_11")

[cjbj]

What are the python-oracledb Thin mode features you need/want to use with ADW?

Overall I suggest you either stick with Thick mode in both scenarios, or don't connect to the two DBs in the same process.

Your scenario is a strong hint that it's time to upgrade from 11g ! Since DB 11g is in its final stages of support (and that is only 'upgrade support'), and we have an ever-growing test matrix, the python-oracledb team has started serious talk about what level of 11g support will be available with new driver features. We have to draw a line somewhere.

[analisis1data]

The solution could be to use thin mode for both databases, but Oracle DB 11g requires thick mode. That database is being upgraded to ADW right now, but it will take a lot of time.

I tried to use both in thick mode, but since I have initialized oracledb with oracledb.init_oracle_client(lib_dir="....instantclient_21_11") for 11g, it triggers the error 'Oracle Client library has already been initialized'.

Running it in another process is not an option; I need to use both in the same script, and multiprocessing is complex. Is there an easy way to handle this global state? Or can I duplicate the library so it appears like a different one?

[anthony-tuininga]

You can only call init_oracle_client() once. If you do call it multiple times, it must be called with the same parameters. This is a restriction of thick mode as it uses global state for a number of parameters. You should, however, be able to use one set of client libraries for both databases. There is no need (or ability!) to use different client libraries for the two different databases.

As for the restriction that python-oracledb has implemented that only thick mode or thin mode can be used in one process, that was primarily done to ease testing and to ensure that there weren't any unexpected interactions between thin mode and thick mode. You can modify the code to eliminate that restriction by removing the errors raised in get_manager() in the src/oracledb/driver_mode.py file -- but you're on your own there! As Chris suggested, use thick mode for both (for now) and then migrate to thin mode once your database has been upgraded from 11g.

[analisis1data]

Hello, thank you for your answer.
I'm trying to use both database connections in thick mode within the same script.

First, Oracle 11g

import getpass
import oracledb
import os
os.environ['LD_LIBRARY_PATH'] = '/home/instantclient_19_26/'
print(os.environ.get("LD_LIBRARY_PATH"))

username="...."
userpwd = getpass.getpass("Enter password: ")
host = "..."
port = ....
sid = "...."

oracledb.init_oracle_client()

conn = oracledb.connect(user=username, password=userpwd, host=host, port=port, sid=sid)

Then I execute this to connect to ADW:

DSN = "adw<DATABASENAME>_medium"
wallet_path = "/home/Wallet_ADW<DATABASENAME>"
os.environ['TNS_ADMIN'] = wallet_path

oracledb.connect(
                user = os.environ.get("USER_ORACLE"),
                password = os.environ.get("PASSWORD"),
                dsn = DSN,
                config_dir = wallet_path
            )

But it throws "DatabaseError: ORA-28759: failure to open file"

If I try to connect independently, it works without any issues.

[anthony-tuininga]

But it throws "DatabaseError: ORA-28759: failure to open file"

That error generally occurs because the wallet location defined in sqlnet.ora is invalid.

Note that if you may have to put the wallet location in the connect string if you are going to use different wallets for each connection.

[cjbj]

os.environ['LD_LIBRARY_PATH'] = '/home/instantclient_19_26/'

This doesn't work for Linux processes, you must set the library search path before the process starts. This is true for all processes, not just Python. If you don't have any other Oracle software on the machine, then use the ldconfig method for setting the library loading search path.

[cjbj]

@analisis1data I'll close this now. Let us know if you have any further questions.