Closed deymundson closed 2 months ago
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.
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()
?
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.
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!
Thanks! That looks like it should work perfectly. Do you have a ballpark for when this will be delivered in a release?
It should be sometime in the next couple of weeks -- but as always, such dates are subject to change!
This was made available in python-oracledb 2.1.0.
What versions are you using?
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.
Is it an error or a hang or a crash? Error
What error(s) or behavior you are seeing? When calling
cursor.executemany()
with an anonymous PL/SQL block, the value ofcursor.rowcount
is always0
. 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. Callingcursor.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
I'm expecting it to be:
Does your application call
init_oracle_client()
? YesInclude a runnable Python script that shows the problem. SQL
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; /