oracledb.thick_impl.ThickVarImpl._get_lob_value: AttributeError: 'list' object has no attribute '_impl'

[KTBL-KristofferSchneider]

Hi!

Until recently we have used python-oracledb version 2.0.1 and now upgraded to 3.4.2. But with the updated version, we are running into Exceptions when executing multiple select queries, which include CLOB columns, with a single cursor object. To ensure it's not because of some strange side effects, I have written a small Error PoC. Below you can see the output on my local machine (Windows) including additional environment information. This exception appears just as is one debian as well. The script is attached at the end.

// EDIT: some additional explanation to the error:
The error appears only, if the same cursor object is used within the for loop to query different elements of the table containing an CLOB.
The first query returns just as expected, but any following query leads to the stacktrace below.
It always fails on the second call to fetchone().Just run the script yourself and you'll see.

Output poc.py:

platform.platform()='Windows-11-10.0.26200-SP0'
(sys.maxsize > 2**32)=True
platform.python_version()='3.14.3'
oracledb.clientversion()=(21, 3, 0, 0, 0)
oracledb.__version__='3.4.2'
con.version='19.27.0.0.0'
Creating tables..✔️
Inserting data..✔️
Producing error..data=(0, [{'foo': 'bar', 'bar': 'baz'}])
Traceback (most recent call last):
  File "C:\Users\anon\Git\oracledb-clob-error-poc\poc.py", line 127, in main
    produce_error(con)
    ~~~~~~~~~~~~~^^^^^
  File "C:\Users\anon\Git\oracledb-clob-error-poc\poc.py", line 88, in produce_error
    data = cursor.fetchone()
  File "C:\Users\anon\Git\oracledb-clob-error-poc\.venv\Lib\site-packages\oracledb\cursor.py", line 1010, in fetchone
    return self._impl.fetch_next_row(self)
           ~~~~~~~~~~~~~~~~~~~~~~~~~^^^^^^
  File "src/oracledb/impl/base/cursor.pyx", line 575, in oracledb.base_impl.BaseCursorImpl.fetch_next_row
  File "src/oracledb/impl/base/cursor.pyx", line 254, in oracledb.base_impl.BaseCursorImpl._create_row
  File "src/oracledb/impl/thick/var.pyx", line 194, in oracledb.thick_impl.ThickVarImpl._get_scalar_value
  File "src/oracledb/impl/thick/var.pyx", line 510, in oracledb.thick_impl.ThickVarImpl._transform_element_to_python
  File "src/oracledb/impl/thick/var.pyx", line 172, in oracledb.thick_impl.ThickVarImpl._get_lob_value
AttributeError: 'list' object has no attribute '_impl'
✔️
Deleting data..✔️
Dropping table..✔️

poc.py:

#! /bin/python3
from collections import namedtuple

import oracledb
import json
import sys
import platform
import traceback

# - # - # - # - # - # - # - # - # - # - # - # - # - # - # - # - # - # - # - # - # - # - # - # -
# Bug / Error PoC when fetching multiple CLOBS iteratively using a single cursor object
# Dependencies of this PoC are provided in the pyproject.toml in python poetry style.
#
# The table simply consists of two columns: an ID and a CLOB column to store JSON.
# Querying content of the table with a single cursor, but split into multiple queries results
# in an error `AttributeError: 'list' object has no attribute '_impl'`  within
# ```
# File "src/oracledb/impl/thick/var.pyx", line 172, in oracledb.thick_impl.ThickVarImpl._get_lob_value
# ```
# This error does not appear in case each query is run using a new cursor. This
# error did not appear in python-oracledb 2.0.1. But now with python-oracledb 3.4.2 it pops up
# The full stacktrace is:
# ```
# Traceback (most recent call last):
#   File "C:\Users\anon\Git\oracledb-clob-error-poc\poc.py", line 101, in <module>
#     main()
#     ~~~~^^
#   File "C:\Users\anon\Git\oracledb-clob-error-poc\poc.py", line 91, in main
#     produce_error(con)
#     ~~~~~~~~~~~~~^^^^^
#   File "C:\Users\anon\Git\oracledb-clob-error-poc\poc.py", line 66, in produce_error
#     data = cursor.fetchone()
#   File "C:\Users\anon\Git\oracledb-clob-error-poc\.venv\Lib\site-packages\oracledb\cursor.py", line 1010, in fetchone
#     return self._impl.fetch_next_row(self)
#            ~~~~~~~~~~~~~~~~~~~~~~~~~^^^^^^
#   File "src/oracledb/impl/base/cursor.pyx", line 575, in oracledb.base_impl.BaseCursorImpl.fetch_next_row
#   File "src/oracledb/impl/base/cursor.pyx", line 254, in oracledb.base_impl.BaseCursorImpl._create_row
#   File "src/oracledb/impl/thick/var.pyx", line 194, in oracledb.thick_impl.ThickVarImpl._get_scalar_value
#   File "src/oracledb/impl/thick/var.pyx", line 510, in oracledb.thick_impl.ThickVarImpl._transform_element_to_python
#   File "src/oracledb/impl/thick/var.pyx", line 172, in oracledb.thick_impl.ThickVarImpl._get_lob_value
# AttributeError: 'list' object has no attribute '_impl'
# - # - # - # - # - # - # - # - # - # - # - # - # - # - # - # - # - # - # - # - # - # - # - # -

CREATE_TABLE_DDL = """
 create table CLOB_ERROR_POC
 (
   ID      number(20, 0)   primary key,
   DATA    clob            not null,
   constraint POC_DATA_JSON_CHECK check (
       DATA is json format json
   )
 )
"""

def create_table(con: oracledb.Connection):
    with con.cursor() as cursor:
        cursor.execute(CREATE_TABLE_DDL)
    con.commit()


def drop_table(con: oracledb.Connection):
    with con.cursor() as cursor:
        cursor.execute('drop table CLOB_ERROR_POC')
    con.commit()


def insert_data(con: oracledb.Connection):
    json_string = json.dumps(
        [{"foo": "bar", "bar": "baz"}]
    )
    with con.cursor() as cursor:
        for id_value in range(0, 5):
            cursor.execute(
                'insert into CLOB_ERROR_POC (ID, DATA) values (:id_value, :json_string)',
                id_value=id_value, json_string=json_string
            )
    con.commit()


def produce_error(con: oracledb.Connection):
    factory = namedtuple('CLOB_ERROR_POC', ['ID', 'DATA'])
    with con.cursor() as cursor:
        for id_value in range(0, 5):
            # The first query run will be successful, but the second call to
            # fetchone() will produce an error.
            cursor.rowfactory = factory
            cursor.execute('select ID, DATA from CLOB_ERROR_POC')
            data = cursor.fetchone()
            print(f"{data=}")


def delete_data(con: oracledb.Connection):
    with con.cursor() as cursor:
        cursor.execute('delete from CLOB_ERROR_POC')
    con.commit()


def main():
    oracledb.init_oracle_client()
    params = oracledb.ConnectParams(host='set_host_here',
                                    port=1521,
                                    sid='set_sid_here')
    session_pool = oracledb.create_pool(user='set_user_here',
                                        password='set_pw_here',
                                        dsn=params.get_connect_string())

    print(f"{platform.platform()=}" )
    print(f"{(sys.maxsize > 2**32)=}")
    print(f"{platform.python_version()=}")
    print(f"{oracledb.clientversion()=}")
    print(f"{oracledb.__version__=}")

    with session_pool.acquire() as con:
        print(f"{con.version=}")
        print("Creating tables..", end="")
        create_table(con)
        print("✔️")

    with session_pool.acquire() as con:
        print("Inserting data..", end="")
        insert_data(con)
        print("✔️")

    with session_pool.acquire() as con:
        print("Producing error..", end="")
        try:
            produce_error(con)
        except Exception as e:
            traceback.print_exc()
        print("✔️")

    with session_pool.acquire() as con:
        print("Deleting data..", end="")
        delete_data(con)
        print("✔️")

    with session_pool.acquire() as con:
        print("Dropping table..", end="")
        drop_table(con)
        print("✔️")


if __name__ == '__main__':
    main()

pyproject.toml:

[project]
name = "oracledb-clob-error-poc"
version = "0.1.0"
description = ""
authors = [
    {name = "",email = ""}
]
requires-python = "^3.14"
dependencies = [
   "oracledb (==3.4.2)"
]


[build-system]
requires = ["poetry-core>=2.0.0,<3.0.0"]
build-backend = "poetry.core.masonry.api"

[cjbj]

While your use case seems to have uncovered something to be fixed in python-oracledb (@anthony-tuininga will have to confirm), there may also be a DB or Oracle Client problem. If you look at previous issues reported on this repo, you can see some where it appears the DB or client libs aren't sending back what it is expected for IS JSON.

So a basic suggestion is to update your Oracle Client libraries which are about 5 years old. At this stage of the 26ai release, I wouldn't just update to the latest 21c. Instead I recommend you should either use 26ai client libs if you want thick mode features (such as pool shrinkage improvements), or use 19c. Both these are "long term support" releases.