How to use different Oracle wallets to connect to different databases?

[dmitrypol]

I have wallet creds for my OCI ATP and ADW instances. I am able to connect to one or the other from my Python app by specifying TNS_ADMIN and WALLET_LOCATION Here is my connection:

ORCL_CONN = cx_Oracle.connect(ORCL_USER, ORCL_PASSWORD, dsn='...', encoding='UTF-8')

But does anyone have experience connecting to 2 Oracle servers via 2 separate connections with 2 wallets? How can I tell cx_Oracle to use specific wallet? Even if I combine connection strings from 2 different tnsnames.ora files I still need to have separate cwallet.sso files (edited)

[dmitrypol]

I was able to “solve” my immediate issue by specifying regional wallet which I use to connect to both instances but I wish there was a simple way to specify which cwallet.sso to use for each connection.

[anthony-tuininga]

If you're looking for how to specify the wallet location for a specific connection, you can take a look at the EZ Connect Plus documentation which will show you how to do just that -- so long as you have Oracle Client 19 or higher.

Note there is a current restriction that you can't specify a different wallet location for two different connections within the same process. @cjbj can provide more detail on that when he is available.

[ppattnayak]

@anthony-tuininga Has the restriction of one wallet for all connections within the same process been lifted? I have a use case where I need to load data into multiple regions and it seems I need to invoke the load once per region.

[anthony-tuininga]

There has been some work on this and there is more ongoing, but as far as I know this restriction has not yet been lifted. @cjbj may know more about whether this is true.

[onmp]

20C client can use EZ Connect Plus to connect to the database server using a different wallet for each connection from the same process.

[anthony-tuininga]

Thanks, @onmp. 20c client is not yet available, though, I believe? If it is available, can you point the user to the location they can acquire such a client? Thanks!

[anthony-tuininga]

This set of code can be used with 20c client as a convenient wrapper to avoid having to manually edit configuration files. I provide it here in the hopes that it might be useful as a starting point:

import cx_Oracle
import os
import tempfile
import zipfile

USER_NAME_1 = "admin"
PASSWORD_1 = "password"
DSN_1 = "tns_1"
CLOUD_CONFIG_1 = "Wallet_1.zip"

USER_NAME_2 = "admin"
PASSWORD_2 = "password"
DSN_2 = "tns_2"
CLOUD_CONFIG_2 = "Wallet_2.zip"

class Connection(cx_Oracle.Connection):

    def __init__(self, *args, cloud_config=None, **kwargs):
        self.temp_dir = None
        self.tns_entries = {}
        if cloud_config is not None:
            self._setup_cloud_config(cloud_config)
            if len(args) > 2:
                dsn = args[2]
            else:
                dsn = kwargs.get("dsn")
            if dsn in self.tns_entries:
                dsn = self.tns_entries[dsn]
                if len(args) > 2:
                    args = args[:2] + (dsn,) + args[3:]
                else:
                    kwargs = kwargs.copy()
                    kwargs["dsn"] = dsn
        super(Connection, self).__init__(*args, **kwargs)

    def __del__(self):
        if self.temp_dir is not None:
            self.temp_dir.cleanup()

    def _setup_cloud_config(self, cloud_config):

        # extract files in wallet zip to a temporary directory
        self.temp_dir = tempfile.TemporaryDirectory()
        zipfile.ZipFile(cloud_config).extractall(self.temp_dir.name)

        # parse tnsnames.ora to get list of entries and modify them to include
        # the wallet location
        fname = os.path.join(self.temp_dir.name, "tnsnames.ora")
        for line in open(fname):
            pos = line.find(" = ")
            if pos < 0:
                continue
            name = line[:pos]
            entry = line[pos + 3:].strip()
            wallet_entry = "(WALLET_LOCATION=%s)" % self.temp_dir.name
            entry = entry[:-2] + wallet_entry + entry[-2:]
            self.tns_entries[name] = entry


def test_connection(user_name, password, dsn, cloud_config):
    conn = Connection(username=user_name, password=password, dsn=dsn, cloud_config=cloud_config)
    cursor = conn.cursor()
    cursor.execute("select sys_context('userenv', 'service_name') from dual")
    result, = cursor.fetchone()
    print(result)


if __name__ == "__main__":
    test_connection(USER_NAME_1, PASSWORD_1, DSN_1, CLOUD_CONFIG_1)
    test_connection(USER_NAME_2, PASSWORD_2, DSN_2, CLOUD_CONFIG_2)

[cjbj]

20c DB is preview in Oracle Cloud; there's no downloadable software.

[cjbj]

Since this feature is out of control of cx_Oracle, and the direction of the Oracle Client is known, I'll close this issue.

[pmaheshwari123]

Can anyone help me to connect to database by python, using oracle wallet creds in a detailed way?

The location of wallet is in remote linux server, and I want to execute python script from there.

[cjbj]

@pmaheshwari123 this is an old, closed issue. Please review the doc, and google for info e.g like https://blogs.oracle.com/opal/how-connect-to-oracle-autonomous-cloud-databases. If you then have further questions, please open a new issue and share code that you are using, and more info about the wallet (there are many kinds). Thanks.

[cjbj]

For future readers, as (currently) noted on https://www.oracle.com/database/technologies/instant-client.html, to connect to multiple Oracle Autonomous Database Instances:

To connect to multiple Oracle Autonomous Database instances from within one application process, use Oracle Instant Client 19.17, or later, or use Instant Client 21c.