Couple Thick/Thin mode to the Connection, not the Application

[mkmoisen]

Today, an application is either in thick mode or in thin mode. All connections within the application must be one or the other.

I have a case where I would like to run a single application that uses thick mode for certain schema/databases, while using thin mode for other schema/databases.

Is this a feasible change, or would it be a large burden?

---

I have an application that connects to dozens of schemas across dozens of Oracle databases. Some of these schemas/dbs are very old are are using a password verifier that is not supported by Thin Mode. It unfortunately is challenging to work with the owners of the schemas and the DBAs to simply reset the passwords. In addition I would like to be able to use QCN for just one table, which is not supported in thin mode.

You might argue that I should just use thick mode. However, I have found thin mode to be preferable in a number of cases. For example AQ in thick mode seems to have an issue where if the session disconnects, the python thread is never notified and doesn't fail with an exception, it will just hang forever. This problem does not exist in thin mode. The workaround is to not use wait forever, and instead wait for ~30 minutes and then reconnect; however to me this somewhat defeats the purpose of using AQ which is to have threads that only wake up when they need to do work. And in general I have noticed a modest performance improvement by switching to thin mode.

So it would be nice in my case to be able to create a connection (or pool) in either thin or thick mode, instead of having a global thick or thin mode for the entire application.

Thank you.

[anthony-tuininga]

We had this discussion back when we first introduced thin mode and @cjbj argued that allowing for concurrent thick and thin connections in the same process might have unexpected behaviors under certain circumstances and would require additional testing resources to ensure that these scenarios were covered.

You can go into the src/oracledb/driver_mode.py file and simply comment out the lines in get_manager() that raise the exceptions. That will allow you to run concurrent thick and thin mode connections -- and you can tell us whether that scenario works well for you. And we can discuss this request further internally and get back to you.

[cjbj]

I'm still not in favour of this, for the same reasons. The general Oracle driver trend is towards having Thin mode only e.g. JDBC-OCI and unmanaged ODP.NET have been deprecated. I'd prefer to spend effort on achieving that goal for python-oracledb.

[mkmoisen]

@cjbj @anthony-tuininga

At least for me, there are two cases where thin mode is not backwards compatible:

• older password verifiers
• QCN

Would it be possible to support older password verifiers? I know this has been discussed repeatedly, but for me this is apparently a blocker.

I completely understand your point that we should simply reset the password and bump the password verifier.

In theory it should not be much effort. In practice though, with a large enough bureaucracy, this is actually quite challenging.

For each schema, and each database, I have to find the right people (far harder than it sounds), have a meeting to explain the situation, which no one seems to understand since most people seem to be unaware of password verifiers, raise a ticket, get DBA approval, etc.

There are some schemas where the original owners have left the company and are essentially running on autopilot, and no one wants to touch these. Even if I explain how I'm just resetting the password to the same value, people are anxious about it.

[cjbj]

@mkmoisen thanks for the detail, and I commiserate. We did discuss the three requests (mode, verifiers, CQN) again with our VP. Of the three, the feasible one (CQN) is high on our roadmap/wishlist, but not scheduled.