Intermittent "ORA-01458: invalid length inside variable character string" error when inserting data into Oracle DB using SQLAlchemy
<p>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. </p>

<p>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:</p>

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

<p>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 <strong>sqlalchemy.engine.base.Connection</strong> object like the following:</p>

<pre><code>try:
    connection.execute(my_table.insert(), records)
except DatabaseError as e:
    connection.execute(my_table.insert(), records)
</code></pre>

<p>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 <strong>do_executemany</strong> of <strong>OracleDialect_cx_oracle</strong> in the sqlalchemy package (sqlalchemy\dialects\oracle\cx_oracle.py):</p>

<pre><code>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)
</code></pre>

<p>Strangely, when I do it this way the second <strong>executemany</strong> 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. </p>

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

<p>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!</p>

python oracle oracle11g sqlalchemy cx-oracle