Fallback by thick mode

[sosoba]

1. What versions are you using?

oracledb: 1.0.3 python: 3.10.5 database: 19.10.0.0.0

2. Is it an error or a hang or a crash?

crash

4. What error(s) or behavior you are seeing?

DPY-2019: python-oracledb thick mode cannot be used because a thin mode connection has already been created

5. Does your application call init_oracle_client()?

Yes. Im trying to use thin mode first and switch to thick when the database is incompatible

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

try:
  connection = oracledb.connect(params=params)
except oracledb.NotSupportedError as err:
  print( err.args[0].message ) # DPY-3015: password verifier type 0x939 is not supported by python-oracledb in thin mode
  oracledb.init_oracle_client()  # Try fallback in thick mode
  connection = oracledb.connect(params=params)

[cjbj]

What you are seeing is the intended behavior. During the initial development we explored how we wanted to expose the two modes, and what the long term driver goal was. There was a solid argument (from @anthony-tuininga) to allow the mode to be selected per-connection, but we decided in the end that this was more complex to explain, and use - and test. It could also add to application complexity, since there are mode differences as noted in the doc and these would need to be carefully handled. Once you have called init_oracle_client() you are in Thick mode and can't go back. If you think your apps are in an environment that needs Thick mode, then use Thick mode all the time.

Our goal is to focus on Thin functionality, with the aim of eventually having full feature support in it (although this is subject to internal decisions about what we're allowed to open source). A practical decision has been to not support older functionality, such as working with DB 11.2. The older password verifier falls into that category too. While what you are asking seems reasonably on its own merits, it doesn't fit in the bigger picture as we see it. And there is a solution: Thick mode.

I'll re-label this as an Enhancement but am not expecting any change. Arguments welcome.

[sosoba]

I receive DPY-3015 on 19c database. The most convenient option would be:

 connection = oracledb.connect(params=params, fallbackThick=True)

Now we'll work around this by calling a python subprocess with a parameter. But it's ugly.

[cjbj]

Noted.

[drkeoni]

I'm running into a similar request. My situation is that we have a few databases and one of them is a legacy database that uses the 10g password verifier that's not supported by the thin client (i.e. #26 ).

We generally run in environments where the thick client is available so we can activate the thick client; however I'd like to use the thin client more and particularly on my local Mac M1 laptop. I've been trying to write tests that use the thin client if available and then fallback to the thick client if they have to.

I've run into a few related problems:

• Is there any way to check if the thick client can be loaded without actually loading it?
• If I try to load the thick client to see if it can be loaded:

def use_thick_client() -> bool:
    try:
        oracledb.init_oracle_client()
    except oracledb.DatabaseError:
        return False
    return True

then I find that this attempted call puts the client in an invalid state on my laptop with no thick client installed. The init_oracle_client() properly throws an exception, but a subsequent attempt to connect to a database fails with DPI-1002: invalid dpiContext handle!. This exception isn't thrown if I never call init_oracle_client().

My top request would be if we could have a method that indicated whether the thick client could (likely) be loaded, without actually loading it. A second request would be having the init_oracle_client() call be more graceful about leaving the client in the thin state if this call fails.

[anthony-tuininga]

Thanks for the detailed information. We will consider these requests!

In the meantime, one option for you is to use the subprocess module to actually make the call to init_oracle_client(). If that succeeds, you know that thick mode is possible -- before contaminating your existing process!

[cjbj]

On Linux, one of our valued internal users has a variant of this script, which works on Linux to fallback to Thin mode if there are no Client libraries available.

import ctypes.util
import sys

try:
    import oracledb
except ImportError:
    import cx_Oracle as oracledb

clntsh = ctypes.util.find_library("clntsh")
if clntsh:
    print(f"Client library found ({clntsh}), using Thick driver")
    oracledb.init_oracle_client()
else:
    print("No Client library found")
    if oracledb.__name__ == "cx_Oracle":
        print("Cannot proceed, cx_Oracle requires Instant Client libraries")
        sys.exit(1)
    else:
        print("Using oracledb Thin client")

[cjbj]

Here are some thoughts about what we plan. Comments and additional info are welcome.

• Only a successful connect() (or equivalent internal pool connection creation) will set the internal flag saying python-oracledb is now in Thin mode forever. This will mean if connection fails with errors about protocols, you can then call init_oracle_client().

• Only a successful init_oracle_client() call will set the internal flag saying python-oracledb is now in Thick mode forever. In this case, init_oracle_client() can be called again with different arguments. There might be some unforseen cases (platforms?) where a library loading failure doesn't result in a clean unloading, but the general behavior would let you use a loop over init_oracle_client() and pass in different potential client library locations (on non Linux platforms), or fallback to a usable Thin mode.

[anthony-tuininga]

Looking into this, I think a small tweak is needed because pool connection creation is deferred, possibly indefinitely (when min=0). So this is what I intend to implement:

• a successful first call to init_oracle_client() will set the internal flag saying python-oracledb is in Thick mode forever.
• a successful first call to connect() or create_pool() will set the internal flag saying python-oracledb is in Thin mode forever (but note that create_pool() doesn't actually create any connections directly as they are created in a background thread).

@sosoba, @drkeoni, will this work sufficiently for you?

[anthony-tuininga]

I've created a patch to support this. If you are able to build and test, that would be appreciated! Thanks.

[drkeoni]

@anthony-tuininga @cjbj Sorry to come back to this late.

All of this information has been really helpful and I appreciate the attention to this issue. I haven't implemented the find_library trick, but I like the looks of it.

These proposed changes sound good to me, thanks!

[cjbj]

With Anthony's changes you won't need find_library() because your script can check (or ignore) if init_oracle_client() fails, which will leave you in Thin mode.

[anthony-tuininga]

Included in python-oracledb 1.1.1 which was just released.

[end1ess1]

Hi everyone! Can you do the same for oracledb.connect_async()?

[anthony-tuininga]

No, as asyncio mandates thin mode (as noted on #316).