cursor.rowcount is always zero when executing PL/SQL · Issue #283 · oracle/python-oracledb (original) (raw)

  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.

  1. Is it an error or a hang or a crash?
    Error
  2. 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
  1. Does your application call init_oracle_client()?
    Yes
  2. 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,))