Exception during insert using executemany() and a PL/SQL statement returns a rowcount of 0 · Issue #285 · oracle/python-cx_Oracle (original) (raw)

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."