Thin client in/out object does not persist with `executemany`

[Aetylus]

1. What versions are you using?

19.28.0.0.0
platform.platform: Windows-10-10.0.22631-SP0
sys.maxsize > 2**32: True
platform.python_version: 3.11.15
oracledb.__version__: 4.0.2

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

It's neither a hang or a crash, but a behavior issue.

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

Given the following package body/spec:

create or replace package array_test is
   type t_log_message is record (
      status varchar2(30),
      id varchar2(9),
      message varchar2(32767)
   );
   type t_log_messages is table of t_log_message index by binary_integer;

   procedure p_add_log_message(
      i_status varchar2,
      i_id varchar2,
      i_message varchar2,
      io_log_messages in out nocopy t_log_messages
   );
end;
/

create or replace package body array_test is
   procedure p_add_log_message(
      i_status varchar2,
      i_id varchar2,
      i_message varchar2,
      io_log_messages in out nocopy t_log_messages
   ) is
      l_index binary_integer := io_log_messages.last();
   begin
      if l_index is null then
         l_index := 0;
      else
         l_index := l_index + 1;
      end if;
      io_log_messages(l_index).status := i_status;
      io_log_messages(l_index).id := i_id;
      io_log_messages(l_index).message := i_message;
   end;
end;
/

The output of the following Python code is not as expected:

from getpass import getpass

import oracledb as db

size = 4
connection = db.connect(user='tom', password=getpass(), dsn='ORCL')
cursor = connection.cursor()

LogMessages = connection.gettype('ARRAY_TEST.T_LOG_MESSAGES')
log_messages = LogMessages.newobject()

data = []
for i in range(0, size):
    data.append(['INFO', str(i + 1), 'Hello world', log_messages])

cursor = connection.cursor()

cursor.executemany('''\
begin
    array_test.p_add_log_message(
        i_status => :1,
        i_id => :2,
        i_message => :3,
        io_log_messages => :4
    );
    array_test.p_add_log_message(
        i_status => :1,
        i_id => :2,
        i_message => :3 || ' 2',
        io_log_messages => :4
    );
end;
''', data)

for key, value in log_messages.asdict().items():
    print({
        key: {
            'status': value.STATUS,
            'id': value.ID,
            'message': value.MESSAGE,
        }}
    )

It seems the T_LOG_MESSAGES object created is passed in and out the first time the PL/SQL block is called, but does not seem to persist updates in any subsequent calls. This results in only the first and last calls affecting the T_LOG_MESSAGES object, as seen in the actual output:

{0: {'status': 'INFO', 'id': '1', 'message': 'Hello world'}}
{1: {'status': 'INFO', 'id': '1', 'message': 'Hello world 2'}}
{2: {'status': 'INFO', 'id': '4', 'message': 'Hello world'}}
{3: {'status': 'INFO', 'id': '4', 'message': 'Hello world 2'}}

I would expect the output to be:

{0: {'status': 'INFO', 'id': '1', 'message': 'Hello world'}}
{1: {'status': 'INFO', 'id': '1', 'message': 'Hello world 2'}}
{2: {'status': 'INFO', 'id': '2', 'message': 'Hello world'}}
{3: {'status': 'INFO', 'id': '2', 'message': 'Hello world 2'}}
{4: {'status': 'INFO', 'id': '3', 'message': 'Hello world'}}
{5: {'status': 'INFO', 'id': '3', 'message': 'Hello world 2'}}
{6: {'status': 'INFO', 'id': '4', 'message': 'Hello world'}}
{7: {'status': 'INFO', 'id': '4', 'message': 'Hello world 2'}}

4. Does your application call init_oracle_client()?

No. It behaves as expected in thick mode, if oracledb.init_oracle_client() is called before a connection is made.

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

See question 3.

[anthony-tuininga]

This is expected. Thick mode (in the Oracle Client libraries) internally performs the execute once for each iteration, so the results are seen after each iteration and those results passed in to the next iteration. In thin mode, however, the first iteration is done independently in order to determine the direction of all bind variables. Once that is complete, however, all subsequent iterations are sent at once -- which is a significant performance boost. There are a couple of ways to approach this issue:

• document the behavior; you can use a loop for situations where the results of one iteration need to be injected into the next iteration
• detect the usage of in/out variables and deliberately send each iteration one at a time just like thick mode (reduces performance)

I can see arguments in favor of both approaches. Any comments?

[Aetylus]

I can see it going either way too, but I think at least personally, my preference would be the second for a couple reasons:

1. I feel like the output between thin and thick modes should match as often as reasonably possible. Documenting behavioral differences that cause output to be different should be left as a last resort.
2. This enables frameworks and libraries that may be using executemany to perform the same regardless of whether in thick or thin modes. In these situations, a user might not have the option to use a loop without having to bypass calling a library or framework entirely.

Not sure if my perspective on this is correct, but that's how I see it at the moment at least.

[anthony-tuininga]

Makes sense to me. I'll go ahead and implement the second option when I get some time.