search

oracle / python-oracledb

Python driver for Oracle Database conforming to the Python DB API 2.0 specification. This is the renamed, new major release of cx_Oracle
https://oracle.github.io/python-oracledb
Other
308 stars 61 forks source link

Strange DPY-5002 error with executemany batcherrors #262

Closed anthony-tuininga closed 6 months ago

anthony-tuininga commented 7 months ago

Discussed in https://github.com/oracle/python-oracledb/discussions/261

Originally posted by **vinidmpereira** December 4, 2023 Hello all, I'm currently having a weird problem in Airflow using OracleHook, i know this forum pertains to oracledb but i think this is where i need help. So i've been writing a large migrating script to unify multiple similar databases into a singular one, and in one of the migrations i've been hit with: `oracledb.exceptions.InternalError: DPY-5002: internal error: read integer of length 3 when expecting integer of no more than length 2` And this error only occurs in one of the 40+ databases i'm currently migrating, it's such a weird error and i can't find anything about this specific error, theres another topic where someone is currently having a similar mistake but with a DPY-5003 error, and i don't know if they are connected.
anthony-tuininga commented 7 months ago

I was table to replicate this. The protocol only allows for 65535 errors. If you exceed this value you get this error in thin mode. In thick mode you get invalid results (the number of batch errors is simply truncated by looking at the lowest 16 bits of the value) -- except when the number of batch errors is a multiple of 65535, in which case you get ORA-38902: errors in array DML exceed 65535. As such, this is a bug in the database and will need to be fixed there. I will, however, correct the strange error in thin mode and ensure a more meaningful error is raised!

The issue can be avoided by always ensuring that you never send more than 65535 rows to the database at a time when using batch errors -- or by ensuring that the number of errors never exceeds 65535 in some other fashion.

A simple test case that demonstrates the problem:

create table issue_262 (
    IntCol number(9) not null,
    StringCol varchar2(50) not null,
    constraint TestTempTable_pk primary key (IntCol)
);

with this Python script:

import oracledb

# oracledb.init_oracle_client()

conn = oracledb.connect("user/password@host/service_name")
cursor = conn.cursor()

data = [(1, None)] * (65537)
cursor.executemany("insert into issue_262 values (:1, :2)", data,
                   batcherrors=True)
errors = cursor.getbatcherrors()
print("found", len(errors), "batch errors")
anthony-tuininga commented 7 months ago

I have pushed a patch that should correct this bug. If you are able to build from source you can verify that it works for you.

anthony-tuininga commented 6 months ago

The patch has been included in version 2.0.0 which was just released.