Intermittent issues retrieving db user-defined object type

[boerbk82]

General Info

Python oracledb version: 1.3.2
Database Info

• Platform: Linux x86 64-bit
• Version: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0

Problem Description

We are seeing an intermittent issue with retrieving a database user-defined object type from our python FastAPI application. In the below snippet of code, the "gettype...newobject" call occasionally fails with the below exception:

'NoneType' object is not iterable

@app.post("/batch")
def sync_batch(batch_info: BatchHeader):
    with pool.acquire() as connection:
        batch_detail_tbl: oracledb.DbObject = connection.gettype("DATA.BATCH_DETAIL_TBL").newobject()

Out of curiosity, I wondered if splitting up the statement would resolve the issue. Unfortunately, that was not the case and it began intermittently failing on the "newobject" call with the below exception

can't concat NoneType to bytes

@app.post("/batch")
def sync_batch(batch_info: BatchHeader):
    with db.get_connection() as connection:
        batch_header_rec_type: oracledb.DbObjectType = connection.gettype("DATA.BATCH_HEADER_T")
        batch_header_rec: oracledb.DbObject = batch_header_rec_type.newobject()

As I mentioned, this seems to only fail intermittently. Typically, this will succeed after making the call a second time. I don't believe this is network connectivity related, as this is the only service having issues.

I have also verified that the database user-defined object type is setup as expected with the necessary privileges on the database. Below is the definition of that database object type:

CREATE OR REPLACE TYPE DATA.batch_header_t AS OBJECT
(
  batch_number           VARCHAR2(20),
  organization           VARCHAR2(20),
  planned_start_date     TIMESTAMP WITH TIME ZONE,
  planned_cmplt_date     TIMESTAMP WITH TIME ZONE,
  prod_line_location     VARCHAR2(60),
  prod_line_number       NUMBER,
  batch_status           VARCHAR2(30),
  expiration_date        TIMESTAMP WITH TIME ZONE,
  config_batch_number    VARCHAR2(200),
  preprice               VARCHAR2(5),
  deviation_percent      NUMBER,
  production_notes       VARCHAR2(2000),
  formula_name           VARCHAR2(30),
  formula_version        NUMBER,
  owner                  VARCHAR2(20),
  validity_rule_end_date DATE,
  batch_detail batch_detail_tbl
)

Please let me know if anyone can point me in the right direction with this issue.

[anthony-tuininga]

Can you provide the complete traceback? Do you have multiple threads creating objects or is this occurring with a single thread? Can you also supply the definition of the embedded object type (batch_detail_tbl)?

[boerbk82]

Thanks for the reply, Anthony. Below is the full traceback of the "gettype...newobject" call failure.

Traceback (most recent call last):
  File "/usr/local/lib/python3.11/site-packages/starlette/middleware/errors.py", line 162, in __call__
    await self.app(scope, receive, _send)
  File "/usr/local/lib/python3.11/site-packages/asgi_correlation_id/middleware.py", line 90, in __call__
    await self.app(scope, receive, handle_outgoing_request)
  File "/usr/local/lib/python3.11/site-packages/starlette/middleware/base.py", line 108, in __call__
    response = await self.dispatch_func(request, call_next)
               ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
?NoneType                                                                                                                                           
    response = await func(request)
               ^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/fastapi/routing.py", line 237, in app
    raw_response = await run_endpoint_function(
                   ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/fastapi/routing.py", line 165, in run_endpoint_function
    return await run_in_threadpool(dependant.call, **values)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/starlette/concurrency.py", line 41, in run_in_threadpool
    return await anyio.to_thread.run_sync(func, *args)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/anyio/to_thread.py", line 31, in run_sync
    return await get_asynclib().run_sync_in_worker_thread(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/anyio/_backends/_asyncio.py", line 937, in run_sync_in_worker_thread
    return await future
           ^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/anyio/_backends/_asyncio.py", line 867, in run
    result = context.run(func, *args)
             ^^^^^^^^^^^^^^^^^^^^^^^^
  File "/app/api/routers/v1/integration.py", line 174, in sync_batch
    batch_detail_tbl: oracledb.DbObject = connection.gettype("DATA.BATCH_DETAIL_TBL").newobject()
                                          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/oracledb/dbobject.py", line 331, in newobject
    obj_impl = self._impl.create_new_object()
               ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "src/oracledb/impl/thin/dbobject.pyx", line 585, in oracledb.thin_impl.ThinDbObjectTypeImpl.create_new_object
  TypeError: 'NoneType' object is not iterable

We are running the FastAPI python application on a uvicorn server with multiple worker threads, so it is possible that multiple threads are creating objects at the same time.

Below is the definition of the "data.batch_detail_tbl" object type in our database:

CREATE OR REPLACE TYPE DATA.batch_detail_tbl IS TABLE OF batch_detail_t

And one more level down:

CREATE OR REPLACE TYPE DATA.batch_detail_t AS OBJECT
(
  item_code        VARCHAR2(30),
  line_type        NUMBER,
  planned_quantity NUMBER,
  actual_quantity  NUMBER,
  uom_code         VARCHAR2(30),
  consumption_type VARCHAR2(30),
  contribute_to_yield VARCHAR2(1),
  scrap_factor        NUMBER,
  scale_type       VARCHAR2(30)
)