Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://github.com/oracle/python-oracledb/issues/283 below:

cursor.rowcount is always zero when executing PL/SQL · Issue #283 · oracle/python-oracledb · GitHub

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,))

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4