Home Why does cx_Oracle bulk insert fail on Windows but work on Linux?
Reply: 0

Why does cx_Oracle bulk insert fail on Windows but work on Linux?

natethedrummer
1#
natethedrummer Published in 2018-02-13 23:09:36Z

I am using the cx_Oracle executemany() function to insert 500,000 rows into a table in batches of 200,000. The function works on RHEL Linux and fails on Windows 7. The issue persists regardless of user, database, and table. The issue only occurs when inserting more than ~150,000 rows. Please help me understand why this fails on Windows and how to solve the issue.

Python 3.5

cx_Oracle 5.3

Error message is ORA-03114: not connected to ORACLE.

Code is

#   Create an Oracle connection
connection = cx_Oracle.connect(user= user, password= pwd, dsn = tns)
#   Create a cursor
cursor = cx_Oracle.Cursor(connection)
try:
    #   Compile the insert SQL w/ bind variable        
    cursor.prepare(insert_sql)
    time.sleep(0.01)
    #   Execute the insert statement, commit & close connection
    cursor.executemany(None, row_batch)
    time.sleep(0.01)
    connection.commit()
    cursor.close()
    connection.close()
    time.sleep(0.01)  
#   In case of error, still close the connection!
#   This avoids a table lock when accessing the table from a different connection
#   at a later date, if this Python session is still active
except Exception as e:        
    cursor.close()
    connection.close()
    raise Exception(e)        
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.328934 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO