Connection sets itself in a corrupted state after receiving an exception

[Epithumia]

1. What versions are you using?

Oracle DB 19c (commercial) and 23c (free)

Python:

platform.platform: Linux-6.1.0-20-amd64-x86_64-with-glibc2.36
sys.maxsize > 2**32: True
platform.python_version: 3.11.4

Oracledb: oracledb.__version__: 2.1.2

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

Error

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

With a connection opened as conn:

with conn.cursor() as cursor:
    try:
        cursor.execute(
            f"SELECT MYFUNC(:id) FROM DUAL",
            {"id": -1}, # This is expected to raise an excpetion
        )
        results = cursor.fetchone()
        results = json.loads(results[0])
    except Exception as e:
        print(str(e))
        pass
with conn.cursor() as cursor2:
    cursor2.execute(
        f"SELECT MYFUNC(:id) FROM DUAL",
        {"id": 2},
    )
    results = cursor2.fetchone()

This results in

oracledb.exceptions.InternalError: DPY-5002: internal error: read integer of length 40 when expecting integer of no more than length 4

Even if the cursor was closed and a new one is created, the next fetch operation fails because it can't process the result.

6. Does your application call init_oracle_client()?

No (thin mode)

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

Minimal example:

MYFUNC:

CREATE OR REPLACE FUNCTION MYFUNC
(
  PARAM1 IN NUMBER 
) RETURN CLOB AS 
outval CLOB;
BEGIN
  IF PARAM1 < 0 THEN
    RAISE_APPLICATION_ERROR(-20000,'Test');
  END IF;
  SELECT JSON_OBJECT('A' VALUE 1) INTO outval FROM DUAL;
  RETURN outval;
END MYFUNC;

Replace the login/password/host/service_name as needed:

import oracledb
conn = oracledb.connect(
                user=login,
                password=password,
                host=host,
                port=1521,
                service_name=service_name,
            )

with conn.cursor() as cursor:
    try:
        cursor.execute(
            f"SELECT MYFUNC(:id) FROM DUAL",
            {"id": -1},
        )
        results = cursor.fetchone()
        results = json.loads(results[0])
    except oracledb.DatabaseError as e:
        print(str(e))
        # prints:
        # ORA-20000: Test
        # ORA-06512: at "LOGIN.MYFUNC", line 8
        pass
with conn.cursor() as cursor2:
    cursor2.execute(
        f"SELECT MYFUNC(:id) FROM DUAL",
        {"id": 2},
    )
    results = cursor2.fetchone()
# Raises an internal exception:
# Traceback (most recent call last):
#   File "/path/to/test.py", line 63, in <module>
#     results = cursor2.fetchone()
#               ^^^^^^^^^^^^^^^^^^
#   File "/home/user/.pyenv/versions/3.11.4/envs/S204/lib/python3.11/site-packages/oracledb/cursor.py", line 823, in fetchone
#     return self._impl.fetch_next_row(self)
#            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
#   File "src/oracledb/impl/base/cursor.pyx", line 550, in oracledb.base_impl.BaseCursorImpl.fetch_next_row
#   File "src/oracledb/impl/thin/cursor.pyx", line 168, in oracledb.thin_impl.ThinCursorImpl._fetch_rows
#   File "src/oracledb/impl/thin/protocol.pyx", line 437, in oracledb.thin_impl.Protocol._process_single_message
#   File "src/oracledb/impl/thin/protocol.pyx", line 438, in oracledb.thin_impl.Protocol._process_single_message
#   File "src/oracledb/impl/thin/protocol.pyx", line 399, in oracledb.thin_impl.Protocol._process_message
#   File "src/oracledb/impl/thin/protocol.pyx", line 378, in oracledb.thin_impl.Protocol._process_message
#   File "src/oracledb/impl/thin/messages.pyx", line 310, in oracledb.thin_impl.Message.process
#   File "src/oracledb/impl/thin/messages.pyx", line 846, in oracledb.thin_impl.MessageWithData._process_message
#   File "src/oracledb/impl/thin/messages.pyx", line 926, in oracledb.thin_impl.MessageWithData._process_row_data
#   File "src/oracledb/impl/thin/messages.pyx", line 616, in oracledb.thin_impl.MessageWithData._process_column_data
#   File "src/oracledb/impl/base/buffer.pyx", line 808, in oracledb.base_impl.Buffer.skip_sb4
#   File "src/oracledb/impl/base/buffer.pyx", line 208, in oracledb.base_impl.Buffer._skip_int
#   File "src/oracledb/impl/thin/packet.pyx", line 232, in oracledb.thin_impl.ReadBuffer._get_int_length_and_sign
#   File "/home/user/.pyenv/versions/3.11.4/envs/S204/lib/python3.11/site-packages/oracledb/errors.py", line 181, in _raise_err
#     raise error.exc_type(error) from cause
# oracledb.exceptions.InternalError: DPY-5002: internal error: read integer of length 40 when expecting integer of no more than length 4

You need to actually close the connection and reopen a new one for the second execute/fetch to work.

Edit: additional investigation shows this is linked to the function returning a CLOB and/or using the JSON_OBJECT method, because the problem doesn't happen if the python query is SELECT json_serialize(MYFUNC(:id) RETURNING CLOB) FROM DUAL or if the function returns a CLOB generated from string concatenation.

[anthony-tuininga]

I can replicate the issue with your example. Thank you! I'll post again once I know the solution.

[anthony-tuininga]

Good news! I was able to correct the issue and have pushed a patch that corrects it. If you are able to build from source you can verify that it works for you, too.

[anthony-tuininga]

This was included in version 2.2.1 which was just released.