cursor.rowcount is always zero when executing PL/SQL

[deymundson]

1. What versions are you using?

Database: Oracle 19c
platform.platform: Windows-10-10.0.19045-SP0
sys.maxsize > 2**32: True
platform.python_version: 3.11.6
oracledb.__version__: 2.0.1

Originally I encountered the issue in OracleDb 1.4.2 but I decided to try upgrading to the latest version to see if the issue was still present.

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

3. What error(s) or behavior you are seeing?
   When calling cursor.executemany() with an anonymous PL/SQL block, the value of cursor.rowcount is always 0. I expect the value to be number of rows that were processed or, in the case of an error, the number of rows before the error. Calling cursor.executemany() with SQL produces the expected result. I also tried the same code with cx_Oracle 8.3.0 and got the expected result.

The result of the Python script below is

Using oracledb: '2.0.1'
SQL row count: 4
SQL (error) row count: 2
PL/SQL row count: 0
PL/SQL (error) row count: 0

I'm expecting it to be:

Using oracledb: '2.0.1'
SQL row count: 4
SQL (error) row count: 2
PL/SQL row count: 4
PL/SQL (error) row count: 2

4. Does your application call init_oracle_client()?
   Yes

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

create table test (
  id int,
  name varchar(20)
);

grant select, insert, update, delete on test to public;

create or replace package pkg_Test as
  procedure New (id varchar2, name varchar2);
end pkg_Test;
/

grant execute on pkg_Test to public;

create or replace package body pkg_Test is
  procedure New (id varchar2, name varchar2) is
  begin
    insert into test (id, name) values (to_number(id), name);
  end;
end pkg_Test;
/

Python

import oracledb

print("Using oracledb: %r" % (oracledb.__version__,))

oracledb.init_oracle_client()
connection = oracledb.connect(...)

cursor = connection.cursor()

try:
    cursor.executemany(
        "insert into test (id, name) values (to_number(:1), :2)",
        [("1", "test1"), ("2", "test2"), ("4", "bar"), ("3", "test3")],
    )
except:
    pass
finally:
    print("SQL row count: %r" % (cursor.rowcount,))

try:
    cursor.executemany(
        "insert into test (id, name) values (to_number(:1), :2)",
        [("1", "test1"), ("2", "test2"), ("foo", "bar"), ("3", "test3")],
    )
except:
    pass
finally:
    print("SQL (error) row count: %r" % (cursor.rowcount,))

try:
    cursor.executemany(
        "begin pkg_Test.New(:1, :2); end;",
        [("1", "test1"), ("2", "test2"), ("4", "bar"), ("3", "test3")],
    )
except:
    pass
finally:
    print("PL/SQL row count: %r" % (cursor.rowcount,))

cursor = connection.cursor()
try:
    cursor.executemany(
        "begin pkg_Test.New(:1, :2); end;",
        [("1", "test1"), ("2", "test2"), ("foo", "bar"), ("3", "test3")],
    )
except:
    pass
finally:
    print("PL/SQL (error) row count: %r" % (cursor.rowcount,))

[anthony-tuininga]

Apologies for the delay in getting back to you. This is not a bug. The value of rowcount for DML (insert, update, delete and merge) is the number of rows affected. For queries the value of rowcount is the number of rows that have been fetched so far. For all other statements that are executed the value of rowcount is 0. For PL/SQL the value that was returned by the Oracle Client libraries varied depending on whether or not execute() or executemany() was called -- so we elected to simply avoid returning any value at all. The documentation still states that the value of rowcount for PL/SQL is the number of iterations executed -- but that is incorrect and will be updated shortly!

If you need to know the number of rows updated inside a PL/SQL block you can use the PL/SQL expression sql%rowcount immediately after the SQL in question has been executed and pass that value back in an out variable or argument.

[deymundson]

Thanks for the reply. The main use case we've had for the rowcount property is to determine which "row" failed when making a batch call to a PL/SQL procedure. Admittedly, using rowcount for this purpose is already a bit of a workaround, but I cannot find a more direct way of accomplishing this goal. The closest thing I can find would be the batcherrors parameter for executemany(), but that cannot be used with PL/SQL.

Another possible workaround would be to use execute() in a loop, but doing so seriously tanks our performance.

Is there any other way I can get at the row when calling PL/SQL with executemany()?

[anthony-tuininga]

In thick mode using executemany() with PL/SQL actually incurs one round trip for each row of data. In thin mode this is not the case, however. I thought the offset attribute in the error object would contain the row that failed but it would seem that is not the case. I'll look into that further and get back to you.

[anthony-tuininga]

I have just pushed a patch that will populate the offset attribute of exceptions raised when calling executemany() with PL/SQL. I think that should cover your scenario!

[deymundson]

Thanks! That looks like it should work perfectly. Do you have a ballpark for when this will be delivered in a release?

[anthony-tuininga]

It should be sometime in the next couple of weeks -- but as always, such dates are subject to change!

[cjbj]

This was made available in python-oracledb 2.1.0.