IndexError when running executemany() with Python arrays as input

[zykure]

I'm trying to run a PL/SQL procedure that takes array values as input. Since I have to call this procedure many times for my data, I tried to use the executemany() function provided by oracledb to improve performance.

However, I get an error message from oracledb as soon as I try to use more than one dataset in the bind arguments. It doesn't make a difference if I use positional or named binds, and using cursor.setinputsizes() also didn't change anything. I hope that someone can shed some light on what's going wrong here.

---

SQL code:

  begin
      CM_DATA.ADD_ITEM(
          p_public_id    => :public_id,
          p_type_id      => :type_id,
          p_field_names  => :field_names,
          p_field_values => :field_values
      );
  end;

The p_field_names and p_field_values both take an array of strings as input. The p_public_id and p_type_id are numbers, and p_public_id is an IN/OUT variable that will return a value.

---

Bind parameters (example with only 2 datasets):

[
  {
    'public_id': <oracledb.Var of type DB_TYPE_NUMBER with value None>, 
    'type_id': 24, 
    'field_names': ['SERVER_ID', 'DOOCS_LOCATION', 'DOOCS_PROPERTY'], 
    'field_values': ['195181', 'AMC1', 'ACTIVATE']
  }, {
    'public_id': <oracledb.Var of type DB_TYPE_NUMBER with value None>, 
    'type_id': 24, 
    'field_names': ['SERVER_ID', 'DOOCS_LOCATION', 'DOOCS_PROPERTY'], 
    'field_values': ['195181', 'AMC1', 'ALIAS']
  }
]

The public_id parameter is created with cursor.var(int) to allow the IN/OUT behavior. Other parameters are simple Python variables.

---

Relevant code:

# These are the input values to my Python function (add_bulk_items)
type_id = 24
public_ids = [None, None]
bulk_data = [{'SERVER_ID': 195181, 'DOOCS_LOCATION': 'AMC1', 'DOOCS_PROPERTY': 'ACTIVATE'},
             {'SERVER_ID': 195181, 'DOOCS_LOCATION': 'AMC1', 'DOOCS_PROPERTY': 'ALIAS'}]

with connection.cursor() as cursor:
    bind_args, return_vars = [], []
    for data, public_id in zip(bulk_data, public_ids):
        # convert input dict to separate name/value lists
        field_names, field_values = [], []
        for k, v in data.items():
            field_names.append(k.upper())
            if v is None:
                field_values.append(None)
            else:
                field_values.append(str(v))

        public_id_var = cursor.var(int)  # needed for IN/OUT parameter
        public_id_var.setvalue(0, public_id)
        bind_args.append(dict(public_id=public_id_var, type_id=type_id, field_names=field_names, field_values=field_values))

    cursor.setinputsizes(public_id=None, type_id=None, field_names=100, field_values=100)
    cursor.executemany(sql, bind_args, batcherrors=False)

---

The error I'm getting:

  File "/home/.../workspace/.../OracleInterface.py", line 529, in add_bulk_items
    cursor.executemany(sql, bind_args, batcherrors=False)
  File "/home/.../miniforge3/envs/python3.10/lib/python3.10/site-packages/oracledb/cursor.py", line 760, in executemany
    self._impl.executemany(
  File "src/oracledb/impl/thin/cursor.pyx", line 236, in oracledb.thin_impl.ThinCursorImpl.executemany
  File "src/oracledb/impl/thin/protocol.pyx", line 440, in oracledb.thin_impl.Protocol._process_single_message
  File "src/oracledb/impl/thin/protocol.pyx", line 441, in oracledb.thin_impl.Protocol._process_single_message
  File "src/oracledb/impl/thin/protocol.pyx", line 409, in oracledb.thin_impl.Protocol._process_message
  File "src/oracledb/impl/thin/protocol.pyx", line 386, in oracledb.thin_impl.Protocol._process_message
  File "src/oracledb/impl/thin/messages.pyx", line 340, in oracledb.thin_impl.Message.send
  File "src/oracledb/impl/thin/messages.pyx", line 2253, in oracledb.thin_impl.ExecuteMessage._write_message
  File "src/oracledb/impl/thin/messages.pyx", line 2219, in oracledb.thin_impl.ExecuteMessage._write_reexecute_message
  File "src/oracledb/impl/thin/messages.pyx", line 1142, in oracledb.thin_impl.MessageWithData._write_bind_params_row
IndexError: list index out of range

The exception is raised here: https://github.com/oracle/python-oracledb/blob/v2.5.0/src/oracledb/impl/thin/messages.pyx#L1142

Again, running this with only 1 item in the bind list works fine, and so does calling cursor.execute() multiple times. I don't understand what the IndexError is about.

Versions used:

• Python 3.10
• oracledb v2.5.0

[cjbj]

Before you get too deep into this, review the caveat in the Batch Execution of PL/SQL doc:

If executemany() is used for PL/SQL code that returns OUT binds it will have the same performance characteristics as repeated calls to execute().

[cjbj]

@anthony-tuininga pointed out that the doc note about PL/SQL and OUT binds with executemany() applies to Thick mode, whereas in Thin mode there is a definite advantage to using executemany() in this scenario.

[cjbj]

This may have some useful clues:

with oracledb.connect(user=un, password=pw, dsn=cs) as connection:

    with connection.cursor() as cursor:
        stmts = [
        """create or replace type my_varchar_list as varray(100) of varchar2(100)""",
        """create or replace procedure myproc(p in number, names in my_varchar_list, count out number) as
           begin
             count := p + names.count;
           end;""",
        ]
        for s in stmts:
            cursor.execute(s)
            if cursor.warning and cursor.warning.full_code == "DPY-7000":
                print(cursor.warning)
                print(s)

    type_obj = connection.gettype("MY_VARCHAR_LIST")

    # Single execute()

    with connection.cursor() as cursor:

        n = 100

        obj = type_obj.newobject()
        obj.extend(['Alex', 'Bobbie', 'Charlie'])

        with connection.cursor() as cursor:
            count = cursor.var(oracledb.DB_TYPE_NUMBER)
            cursor.callproc("myproc", [n, obj, count])

            # prints "OUT bind value is 103"
            print(f"OUT bind value is {count.getvalue()}")

    # Using executemany()

    with connection.cursor() as cursor:

        obj1 = type_obj.newobject()
        obj1.extend(['Alex', 'Bobbie'])

        obj2 = type_obj.newobject()
        obj2.extend(['Charlie', 'Dave', 'Eric'])

        obj3 = type_obj.newobject()
        obj3.extend(['Fred', 'Georgia', 'Helen', 'Ian'])

        data = [
            {"p": 100, "names": obj1},
            {"p": 200, "names": obj2},
            {"p": 300, "names": obj3},
        ]

        count = cursor.var(oracledb.DB_TYPE_NUMBER, arraysize=len(data))
        cursor.setinputsizes(p=None, names=type_obj, count=count)

        cursor.executemany("begin myproc(:p, :names, :count); end;", data)

        # prints "OUT bind value is [102, 203, 304]"
        print(f"OUT bind value is {count.values}")

Output is:

OUT bind value is 103
OUT bind value is [102, 203, 304]

[zykure]

Thanks, this is helpful indeed! Unfortunately my code still gives me an IndexError after I switch to using dedicated types and creating bind variables with newobject(). But my types in Oracle are declared as TABLE OF VARCHAR2(...) and not VARRAY so maybe that is an issue too? I will investigate this a bit more.

After looking at the code in messages.pyx it looks like the condition

var_impl = bind_info._bind_var_impl
if var_impl.is_array:
    ...

is false when I pass an array, but one would expect it's true? I guess that's why the IndexError is raised a few lines down when it tries to access var_impl._values at some invalid index.

[cjbj]

My code snippet works identically if you replace the creation of my_varchar_list with:

"create or replace type my_varchar_list as table of varchar2(100)"

If you try Thick mode you'll probably get a DPI error message - which still won't be too useful. @anthony-tuininga has a task to look at giving a DPY error message in Thin mode.

If you share a properly runnable reproducible case we can help you futher.

[zykure]

Alright, thanks to the code example from @cjbj I was able to get it working.

The main issue apparently was the definition of return variables - instead of passing individual cursor variables with the bind list, they must be (additionally) passed with cursor.setinputsizes() as an array type with correct length. This works now with my procedure that uses in/out parameters and arrays declared as table of varchar2.

I can provide an updated code example later.

[cjbj]

I'm glad you got it going. Doc and samples will be updated soon to help future readers.

[cjbj]

The doc just got pushed to GH. See https://python-oracledb.readthedocs.io/en/latest/user_guide/batch_statement.html#batch-execution-of-pl-sql and https://github.com/oracle/python-oracledb/blob/main/samples/plsql_batch.py

[zykure]

That was super fast, thanks a lot! The sample code also contains my use case and provides a working solution, so I'll mark this as an answer.