Oracle批量插入多行并返回生成主键至Python列表的代码报错求助（ORA-01036）

Got it, let's figure out why your code is throwing that ORA-01036 error and get it working properly. That error almost always points to a mismatch between the bind variables in your SQL and what you're passing from Python—let's break down the issues and fix them step by step.

What's Wrong With Your Current Code #

• Wrong Variable Type for Bulk Returns: You're using a scalar cursor.var() to capture IDs from multiple inserts, but executemany needs an array variable to hold all the returned primary keys (one per row inserted).
• Redundant Table Name in RETURNING Clause: You don't need to write table.col2 in the RETURNING part—just col2 works fine, and including the table name can confuse the bind variable mapping.
• Missing Parameter Mapping: You haven't properly passed the output variable into the executemany call, so cx_Oracle can't link :out_id in the SQL to your Python variable.

Fixed Code Example #

Here's the corrected version that will capture all auto-generated keys into a Python list:

import cx_Oracle

# Assume your connection and cursor are already set up (adjust as needed)
# conn = cx_Oracle.connect("user/password@host:port/service_name")
# cursor = conn.cursor()

# Create an array variable sized to match your batch count
batch_size = 3
out_ids = cursor.arrayvar(cx_Oracle.NUMBER, batch_size)

# Build your batch of input data
batch = [{'val': i} for i in range(batch_size)]

# Clean up the SQL statement
insert_sql = """
    INSERT INTO table (col1) 
    VALUES (:val) 
    RETURNING col2 INTO :out_ids
"""

# Execute with the output variable included in parameters
cursor.executemany(insert_sql, batch, parameters={'out_ids': out_ids})

# Convert the array variable to a Python list
returned_pks = out_ids.getvalue().tolist()
print("Returned primary keys:", returned_pks)

# Don't forget to commit the transaction!
conn.commit()

Key Fixes Explained #

• Array Variable for Bulk Output: cursor.arrayvar() creates a variable that can hold multiple values, which is mandatory when using RETURNING with executemany—each insert returns one key, so we need an array to collect all of them.
• Simplified SQL: Removed the unnecessary table. prefix from col2 in the RETURNING clause to avoid bind variable confusion.
• Explicit Parameter Mapping: By passing parameters={'out_ids': out_ids} to executemany, we tell cx_Oracle exactly which Python variable maps to :out_ids in the SQL.
• Commit the Changes: Always commit after inserts to make sure the data is saved to the database—this is easy to forget but critical.

Extra Tips for Troubleshooting #

If you still hit issues:

• Confirm that col2 is actually your auto-generated primary key (whether it's an identity column, or populated by a sequence trigger).
• Make sure the size of your array variable matches the number of rows you're inserting—if you change the batch size, update batch_size too.
• Check for typos in column names or variable names (it's easy to mix up :val or :out_ids).

内容的提问来源于stack exchange，提问作者e.burenina