Thin mode callproc() hangs with NUMBER inputs and OUT SYS_REFCURSOR; numeric strings succeed

[rsantmyer]

Thin mode callproc() hangs with NUMBER inputs and OUT SYS_REFCURSOR; numeric strings succeed

1. What versions are you using?

[oracledb_ref_cursor_repro.py](https://github.com/user-attachments/files/30933861/oracledb_ref_cursor_repro.py)
[oracledb_ref_cursor_repro.sql](https://github.com/user-attachments/files/30933862/oracledb_ref_cursor_repro.sql)

Oracle Database: 23.26.3.2.0
platform.platform: Linux-6.18.33.1-microsoft-standard-WSL2-x86_64-with-glibc2.39
sys.maxsize > 2**32: True
platform.python_version: 3.12.3
oracledb.__version__: 4.0.2

The self-contained reproduction below was run with python-oracledb 4.0.2.

The original application behavior was also reproduced with python-oracledb
3.4.0 and 4.0.1, but those versions were not used for the self-contained
reproduction.

Oracle Client version is not applicable because this uses Thin mode.

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

It is a hang inside Cursor.callproc(). After Connection.call_timeout is
reached and the driver attempts connection recovery, it raises DPY-4011.

No REF CURSOR fetch occurs before the hang. No process crash or segmentation
fault was observed.

3. What error or behavior are you seeing?

A packaged procedure accepts two NUMBER inputs and returns one
SYS_REFCURSOR.

The result depends on how the two NUMBER parameters are bound:

Input binding	Result
Python integers	Hangs, then DPY-4011
Explicit DB_TYPE_NUMBER variables	Hangs, then DPY-4011
Numeric strings	Returns and fetches immediately

With Python integers, the command is:

cursor.callproc(
    "oracledb_ref_cursor_repro.get_row",
    [1, 3, result],
)

After the configured 5000 ms call timeout and connection recovery attempt, it
raises:

Traceback (most recent call last):
  File "oracledb_ref_cursor_repro.py", line 65, in <module>
    main()
  File "oracledb_ref_cursor_repro.py", line 46, in main
    cursor.callproc("oracledb_ref_cursor_repro.get_row", parameters)
  File "oracledb/cursor.py", line 766, in callproc
    self._call(name, parameters, keyword_parameters)
  File "oracledb/cursor.py", line 98, in _call
    return self.execute(statement, bind_values)
  File "oracledb/cursor.py", line 859, in execute
    impl.execute(self)
  File "src/oracledb/impl/thin/cursor.pyx", line 279, in
    oracledb.thin_impl.ThinCursorImpl.execute
  File "src/oracledb/impl/thin/protocol.pyx", line 501, in
    oracledb.thin_impl.Protocol._process_single_message
  File "src/oracledb/impl/thin/protocol.pyx", line 502, in
    oracledb.thin_impl.Protocol._process_single_message
  File "src/oracledb/impl/thin/protocol.pyx", line 456, in
    oracledb.thin_impl.Protocol._process_message
oracledb.exceptions.DatabaseError: DPY-4011:
the database or network closed the connection
socket timed out while recovering from previous socket timeout

Explicit NUMBER variables produce the same result:

identifier = cursor.var(oracledb.DB_TYPE_NUMBER)
identifier.setvalue(0, 1)

run_id = cursor.var(oracledb.DB_TYPE_NUMBER)
run_id.setvalue(0, 3)

cursor.callproc(
    "oracledb_ref_cursor_repro.get_row",
    [identifier, run_id, result],
)

Binding the same values as strings succeeds:

cursor.callproc(
    "oracledb_ref_cursor_repro.get_row",
    ["1", "3", result],
)

Observed output:

callproc returned in 0.031s
fetch returned in 0.000s: (1, 3)

Calling the procedure entirely from database-side PL/SQL also succeeds.

This appears similar to #169, but it reproduces with python-oracledb 4.0.2
using the self-contained package below.

4. Does your application call init_oracle_client()?

No. It does not call oracledb.init_oracle_client().

The problem occurs in python-oracledb Thin mode:

oracledb.is_thin_mode(): True

5. Runnable reproduction

First create the package:

create or replace package oracledb_ref_cursor_repro as
  procedure get_row(
      p_id in number
    , p_run_id in number
    , p_result out sys_refcursor
  );
end oracledb_ref_cursor_repro;
/

create or replace package body oracledb_ref_cursor_repro as
  procedure get_row(
      p_id in number
    , p_run_id in number
    , p_result out sys_refcursor
  ) is
  begin
    open p_result for
      select p_id as id, p_run_id as run_id
        from dual;
  end get_row;
end oracledb_ref_cursor_repro;
/

Then run:

import os
import platform
import sys
import time

import oracledb


print("platform.platform:", platform.platform())
print("sys.maxsize > 2**32:", sys.maxsize > 2**32)
print("platform.python_version:", platform.python_version())
print("oracledb.__version__:", oracledb.__version__)
print("oracledb.is_thin_mode():", oracledb.is_thin_mode())

connection = oracledb.connect(
    user=os.environ["DB_USER"],
    password=os.environ["DB_PASSWORD"],
    dsn=os.environ["DB_DSN"],
)
connection.call_timeout = 5_000

with connection:
    print("connection.version:", connection.version)

    with connection.cursor() as cursor:
        result = cursor.var(oracledb.DB_TYPE_CURSOR)

        started = time.monotonic()
        cursor.callproc(
            "oracledb_ref_cursor_repro.get_row",
            [1, 3, result],
        )
        print(f"callproc returned in {time.monotonic() - started:.3f}s")

        result_cursor = result.getvalue()
        try:
            print("result:", result_cursor.fetchone())
        finally:
            result_cursor.close()

Expected behavior: Python integers or explicit DB_TYPE_NUMBER variables bind
to the PL/SQL NUMBER parameters and the procedure returns its REF CURSOR.

If the bind configuration is invalid, an immediate actionable exception would
be preferable to a timeout and lost connection.