CODEWITHSUNDEEP
pythonoracle-databaseoracle11gsqlalchemycx-oracle
Travis Lu
Travis Lu

Reputation: 57

Intermittent "ORA-01458: invalid length inside variable character string" error when inserting data into Oracle DB using SQLAlchemy

I am working on a Python 3.5 server project and using SQLAlchemy 1.0.12 with cx_Oracle 5.2.1 to insert data into Oracle 11g. I noticed that many of my multi-row table insertions are failing intermittently with "ORA-01458: invalid length inside variable character string" error.

I generally insert a few thousand to a few tens of thousands of rows at a time, and the data is mostly composed of strings, Pandas timestamps, and floating point numbers. I have made the following observations:

  1. The error occurs on both Windows and Linux host OS for the Python server
  2. The error always occurs intermittently, even when the data doesn't change
  3. If I don't insert floating point numbers, or if I round them, the error happens less often but still happens
  4. If I insert the rows one at a time I don't encounter the error (but this is unacceptable for me performance-wise)

Additionally, I have tried to insert again if I encountered the error. The first thing I tried was to was put a try-except block around where I call execute on the sqlalchemy.engine.base.Connection object like the following:

try:
    connection.execute(my_table.insert(), records)
except DatabaseError as e:
    connection.execute(my_table.insert(), records)

I noticed that using this method the second insertion still often fails. The second thing I tried was to try the same in the implementation of do_executemany of OracleDialect_cx_oracle in the sqlalchemy package (sqlalchemy\dialects\oracle\cx_oracle.py):

def do_executemany(self, cursor, statement, parameters, context=None):
    if isinstance(parameters, tuple):
        parameters = list(parameters)

    # original code
    # cursor.executemany(statement, parameters)

    # new code
    try:
        cursor.executemany(statement, parameters)
    except Exception as e:
        print('trying again')
        cursor.executemany(statement, parameters)

Strangely, when I do it this way the second executemany call will always work if the first one fails. I'm not certain what this means but I believe this points to the cx_Oracle driver being the cause of the issue instead of sqlalchemy.

I have searched everywhere online and have not seen any reports of the same problem. Any help would be greatly appreciated!

Upvotes: 2

Views: 3570

Answers (2)

Itack
Itack

Reputation: 222

I had the same problem, the code would some times fail and some times go through with no error. Apparently my chunk size was to big for buffer, the error did not occur anymore once I reduced the chunk size from 10K to 500 rows.

Upvotes: 1

Travis Lu
Travis Lu

Reputation: 57

We found out that if we replace all the float.nan objects with None before we do the insertion the error disappears completely. Very weird indeed!

Upvotes: 0

Related Questions