Thick Mode cannot share Type between two Connections ORA-21779: duration not active

[mkmoisen]

This bug only exists in thick mode, not thin mode.

Getting a type from the database requires a relatively expensive round trip:

r_rec := conn.gettype('TEST_FOO_PKG.R_REC')

If you have a web server with a connection pool, it would be ideal to create one type at application launch that can be shared between all connections. This way you don't have the performance hit of having to instantiate a new type for each web request.

With thin mode, we can share a type between multiple connections.

With thick mode, this results in ORA-21779: duration not active. I'm using AQ and some other features and cannot switch to thin mode unfortunately.

Would you folks please take a look? Thank you.

CREATE OR REPLACE PACKAGE test_foo_pkg
IS
    TYPE r_rec IS RECORD (
        bar varchar2(128)
    );
    
    FUNCTION baz
    RETURN r_rec;
END test_foo_pkg;
/

CREATE OR REPLACE PACKAGE BODY test_foo_pkg
IS
    
    FUNCTION baz
    RETURN r_rec
    IS
        l_rec r_rec;
    BEGIN
        l_rec.bar := 'hello';
        RETURN l_rec;
    END baz;
END test_foo_pkg;
/

import contextlib
import oracledb
from datetime import datetime

oracledb.init_oracle_client()

assert not oracledb.is_thin_mode()

conn1 = oracledb.connect('user/password@db')
conn2 = oracledb.connect('user/password@db')

r_rec = conn1.gettype('TEST_FOO_PKG.R_REC')


# First connection

cursor1 = conn1.cursor()

l_rec = cursor1.callfunc('TEST_FOO_PKG.BAZ', r_rec)
assert l_rec.BAR == 'hello'

cursor1.close()


# Second connection, attempt to reuse type r_rec

cursor2 = conn2.cursor()

# This raises Connections ORA-21779: duration not active
l_rec = cursor2.callfunc('TEST_FOO_PKG.BAZ', r_rec)

On thin mode it works without issue:

assert oracledb.is_thin_mode
...
l_rec = cursor2.callfunc('TEST_FOO_PKG.BAZ', r_rec)
assert l_rec.BAR == 'hello'

1. What versions are you using?

Oracle Database 19.25.0.0.0
OCI 19.25

platform.platform: Windows-11-10.0.22631-SP0
sys.maxsize > 2**32: True
platform.python_version: 3.13.0
oracledb.version: 2.5.1
oracledb.clientversion(): (19, 25, 0, 0, 0)

Give your database version.

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

Error

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

ORA-21779: duration not active

4. Does your application call init_oracle_client()?

Yes, thick mode.

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

Please see above.

[cjbj]

From memory, this is an OCI limitation.

[cjbj]

@mkmoisen Work on Thin mode AQ support is progressing, but what are the 'other features' that might stop you using Thin mode?

[mkmoisen]

@cjbj

AQ and CQN are the big ones.

In another app, there is a password verifier issue for thin mode. The solution is to simply reset the password on each database to use recent password verifiers, however the problem is that this application connects to dozens of schemas in ~10 different databases with different DBAs and teams. I am working at a large company so it is very difficult to even find out who has permissions to reset the passwords.

[anthony-tuininga]

As Chris noted, this is not a bug but a limitation in thick mode and I am not aware of any way to work around it. On 23ai a new error message is raised: ORA-30757: Type information could not be accessed. I tried with a pool, however, and that works just fine as far as I can tell -- perhaps you can use a pool instead of creating standalone connections?

[mkmoisen]

@anthony-tuininga Would you please explain how come this works in thick mode with a oracledb pool, but not without one?

I confirm that it works with a oracledb pool.

However, it does not work with a SQLAlchemy pool on top of oracledb, unfortunately. It's possible to hook into and customize the SQLAlchemy pool. Given that it works with a oracledb pool, I wonder if I could get it working with SQLAlchemy.

[anthony-tuininga]

The reason it works with a pool is due to the fact that each standalone connection uses a distinct Oracle Environment handle whereas with a pool, the pool and all connections from that pool share an Oracle Environment handle. Thin mode doesn't have this restriction as you noticed.

And yes, there is a way to create a pool and use that within SQLAlchemy. Something like this:

# adjust as needed!
pool = oracledb.create_pool(user="user", password="password",
                            dsn="host:port/service_name",
                            min=1, max=5, increment=1)

engine = sqlalchemy.create_engine("oracle+oracledb://", creator=pool.acquire,
                                  poolclass=sqlalchemy.NullPool)

[cjbj]

Using the driver connection pool as shown has various advantages such as better High Availability support, and lets DRCP optionally be used.

We recently added the SQLALchemy pool example to the python-oracledb doc: Connecting with SQLAlchemy.

[mkmoisen]

I've been using the oracledb pool with SQLAlchemy, seems to work fine for this. Thank you.