Error with fetchall() for clobs with existing but disabled json check constraint and non json conform content

[Lunnaris01]

1. What versions are you using?

platform.platform: Linux-5.4.17-2136.342.5.3.el8uek.x86_64-x86_64-with-glibc2.28
sys.maxsize > 2**32: True
platform.python_version: 3.9.20
oracledb.version: 3.1.1

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

error

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

  File "test.py", line 34, in <module>
    rows = cursor.fetchall()
  File ".venv/lib64/python3.9/site-packages/oracledb/cursor.py", line 777, in fetchall
    row = 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 256, in oracledb.base_impl.BaseCursorImpl._create_row
  File "src/oracledb/impl/thick/var.pyx", line 189, in oracledb.thick_impl.ThickVarImpl._get_scalar_value
  File "src/oracledb/impl/thick/var.pyx", line 444, in oracledb.thick_impl.ThickVarImpl._transform_element_to_python
  File "src/oracledb/impl/thick/var.pyx", line 167, in oracledb.thick_impl.ThickVarImpl._get_lob_value
AttributeError: 'list' object has no attribute '_impl'

4. Does your application call init_oracle_client()?

yes

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

   oracledb.init_oracle_client()
   conn = oracledb.connect(user=..., password=..., dns=...)


    sql = f"""
    SELECT * FROM my_clob_table
    """

    cursor.execute(sql)
    rows = cursor.fetchall()

The quintessence seems to be that we have a CLOB field where config parameters are saved in a manner like this:
[ { "name": "test123", "host": [ "host1", "host4", "host7"], "command_name": "dosomething", "command_params": ["param1", "param2", "param3"] } ]
which causes the fetchall to fail.

[anthony-tuininga]

Can you supply a more complete scenario that demonstrates the problem? Simply fetching from CLOBs doesn't result in this issue at least! Is the issue present when a CLOB contains the supplied data? If a single row with that data in it is fetched does it result in the problem?

[Lunnaris01]

That's correct, I only encountered the issue for a single table where the clob field contained the specificly formatted data while running the script for many tables, many of them including clobs.
From the traceback I would guess the specific syntax of the content of the clob caused a weird interaction. I will try to reproduce the issue with a dummy database and if I can reproduce it I'll check single row fetching as well.

[cjbj]

@Lunnaris01 what is a "poorly formatted clob". Does your CLOB have a check constraint IS JSON?

[Lunnaris01]

The "poorly formatted clob" really just refered to my idea of the syntax/format being the root cause of the issue. While the content seems to be a json or something similar from the text I personally doubt there is going to be a check constraint on it but I'll check it tomorrow and change the title to be less insulting to the json format

[Lunnaris01]

@cjbj good call, seems like there is a check constraint for is json, however it is disabled. When I created a test table with the same structure and turned the check on, attempting to insert the data present in the original table gave an ORA-02290 for not satisfying the check.
Most likely the check was added assuming the data would be formatted in json and then disabled when the check didn't work with the idea of maybe fixing the input data later on and reenableing it, but seems like that wasn't achieved yet.
I dropped the constraint and everything worked fine again.

[cjbj]

I'm glad you are past the problem, but it is an interesting one that would be good to have fixed.

To help us try to reproduce it, can you share the values of the DB version: connection.version, and the client version: oracledb.clientversion()?