Skip to content

Exception during insert using executemany() and a PL/SQL statement returns a rowcount of 0 #285

@joeltoth

Description

@joeltoth

Computronix has recently upgrade cx_Oracle to version 7.1.1 and we have noticed that the rowcount attribute is returning as 0 when a PL/SQL block with a single insert statement is passed into executemany().

    dataToInsert = [
        (100, 'Jim', 'Ross', 'N', 'N'),
        (101, 'John', 'MacLean', 'N', 'N'),
        (102, 'Gary', 'Robertson', 'N', 'N'),
        (102, 'Robert', 'Pierce', 'N', 'N'),       # duplicate key
        (103, 'Bob', 'Burke', 'N', 'N'),
        (104, 'Faith', 'Hope', 'N', 'N'),
        (105, 'Rudy', 'Rudy', 'N', 'N'),
        (105, 'Charles', 'Anderson', 'N', 'N'),    # duplicate key
    ]

    #sqlStatement = "insert into ExternalContacts_t values (:1, :2, :3, :4, :5)"
    plSqlStatement = "begin insert into ExternalContacts_t values (:1, :2, :3, :4, :5); end;"

    try:
        cursor.executemany(plSqlStatement, dataToInsert)
    except cx_Oracle.DatabaseError as e:
        error, = e.args
        print("FAILED with error: %s") % error.message
        print("number of rows which succeeded (rowcount): %s") % cursor.rowcount

If I run the same example but instead pass a SQL statement into executemany(), the rowcount attribute returns the correct number.

This was originally fixed in cx_Oracle version 5.1.2 addessing the following item from the cx_Oracle release notes: "2. Set the row number in a cursor when executing PL/SQL blocks as requested by Robert Ritchie."

  1. What is your version of Python? Is it 32-bit or 64-bit?
    2.7.13 (v2.7.13:a06454b1afa1, Dec 17 2016, 20:53:40) [MSC v.1500 64 bit (AMD64)]

  2. What is your cx_Oracle version?
    7.1.1 & 7.1.2 tested

  3. What exact command caused the problem (e.g. what command did you try to install with)? Who were you logged in as?
    executemany() passing in a PL/SQL block while logged in as the table owner; "sitespecific" in my case.

  4. What error(s) you are seeing?
    row count is being reported as 0 when i would expect it to be reporting 3 which is the number of rows that succeeded before the duplicate key exception.

  5. What OS (and version) is Python executing on?
    Windows 10 (1803/1809)

  6. What is your version of the Oracle client (e.g. Instant Client)? How was it installed? Where is it installed?
    Installed the Oracle client, it is not an instant client.
    Versions: 11.2.0.4 , 12.1.0.2 , 12.2.0.1
    Location: "C:\oracle\product<version>\client"

  7. What is your Oracle Database version?
    11.2.0.4 , 12.1.0.2 , 12.2.0.1

  8. Do you have a small, single Python script that immediately runs to show us the problem?
    If you run the cx_Oracle Samples and change the sqlStatement to a plSqlStatement the error can be reproduced.

Metadata

Metadata

Assignees

No one assigned

    Labels

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions