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()?

[Lunnaris01]

connection.version: 19.26.0.0.0
oracledb.clientversion(): (21, 11, 0, 0, 0)

Sadly when I asked a colleague about the table just to get some more information why it existed and if he knew why there was a disabled json check he later came back to me saying he deleted it because it was apparently obsolete, thus I can't fully replicate the situation.
I tried to replicate the error with a dummy table. I am still able to produce an error with the disabled is json check but it is a different one, I'm not sure why exactly:

  File "/home/.venv/lib64/python3.9/site-p                                                                                                                                                             ackages/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.Base                                                                                                                                                             CursorImpl.fetch_next_row
  File "src/oracledb/impl/base/cursor.pyx", line 256, in oracledb.base_impl.Base                                                                                                                                                             CursorImpl._create_row
  File "src/oracledb/impl/thick/var.pyx", line 189, in oracledb.thick_impl.Thick                                                                                                                                                             VarImpl._get_scalar_value
  File "src/oracledb/impl/thick/var.pyx", line 452, in oracledb.thick_impl.Thick                                                                                                                                                             VarImpl._transform_element_to_python
  File "src/oracledb/impl/base/cursor.pyx", line 135, in oracledb.base_impl.Base                                                                                                                                                             CursorImpl._build_json_converter_fn.converter
  File "/usr/lib64/python3.9/json/__init__.py", line 346, in loads
    return _default_decoder.decode(s)
  File "/usr/lib64/python3.9/json/decoder.py", line 337, in decode
    obj, end = self.raw_decode(s, idx=_w(s, 0).end())
  File "/usr/lib64/python3.9/json/decoder.py", line 353, in raw_decode
    obj, end = self.scan_once(s, idx)
json.decoder.JSONDecodeError: Invalid control character at: line 9 column 33 (ch                                                                                                                                                             ar 227)

I think the gist of it is that despite the is json check being disabled the clob is still interpreted as a json and the fetchall will however assume that it actually is a json
The problem is recreatable when dropping and readding the constraint.

ALTER TABLE "TEST"."python_bugtest" ADD CONSTRAINT json_check_constraint CHECK(test_clob_column IS JSON) DISABLE;
-> fetching will fail
ALTER TABLE "TEST"."python_bugtest" DROP CONSTRAINT json_check_constraint;
-> fetching will work fine.
Note that adding the constraint without the disable naturally produced an error since the data violates the check constraints.

2293. 00000 - "cannot validate (%s.%s) - check constraint violated"
      *Cause: an alter table operation tried to validate a check constraint to
      populated table that had nocomplying values.
      *Action: Obvious

[earlybard]

I'm getting the same error with the following conditions

• Enabled JSON constraint is on the table - ALTER TABLE X ADD CONSTRAINT Y CHECK (Z IS JSON)
• Running python-oracledb in Thick mode.

Either removing the constraint or running in Thin mode fixes the issue - unfortunately I need both!

[cjbj]

@earlybard what Oracle Database & Oracle Client versions are you using? And python-oracledb version?

[earlybard]

@earlybard what Oracle Database & Oracle Client versions are you using? And python-oracledb version?

Oracle Database - 19.0.0.0.ru-2025-04.rur-2025-04.r1 running on Amazon RDS. I've seen it on other non-cloud instances of 19 too.
Oracle Client - https://download.oracle.com/otn_software/linux/instantclient/2380000/instantclient-basic-linux.x64-23.8.0.25.04.zip
python-oracledb - 3.1.0
Python - 3.12.11
Django - 5.2.1

[cjbj]

@earlybard this smells like an Oracle client / DB issue, but maybe python-oracledb can workaround it if we can reproduce it.
What does print(connection.version) give for the DB version?

[earlybard]

@cjbj - '19.27.0.0.0'

Thanks for looking into this!