Connection sets itself in a corrupted state after receiving an exception · Issue #338 · oracle/python-oracledb (original) (raw)

  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
  1. Is it an error or a hang or a crash?

Error

  1. 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.

  1. Does your application call init_oracle_client()?

No (thin mode)

  1. 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

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.