Python script to move data from a SQL server db to an oracle db keeps giving ‘ORA-01036: illegal variable name/number’

So I am using python to pull data from a sql server with a simple select that grabs 15 columns. The data looks like this

2016-06-01 05:45:06.003,5270,240,1,1,0,5000,1,null,null,7801009661561,0,null,null,null

The columns on the oracle table are all number except for the first column which is date. The sizes are all correct.

After I get all the data i run it through this little function to get rid of the pyodbc.row types.

def BuildBindList(recordsToWrite):
    closingRecords = []
    for rec in recordsToWrite:
        closingRecords.append((rec[0], rec[1], rec[2], rec[3], rec[4],  rec[5], rec[6], rec[7], rec[7], rec[8], rec[9], rec[10], rec[11], rec[12], rec[13], rec[14]))
    return closingRecords    

I get a list of tuples.

Then to write to the oracle table I wrote this function that takes in the list of tuples.

    def write_to_table(recordsToWrite):     
        SQL = '''INSERT INTO ####### (DATETIME, ID, TZ, DOMAINID, EVENTNAME, REASONCODE, TARGETID, STATE, KEY, PERIPHERALKEY, RECOVERYKEY, DIRECTION, ROUTERDAY, ROUTERCKEY, ROUTERNUMBER)
                VALUES(:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15)'''
        try:
            trgtcrsr.prepare(SQL)
        except cx_Oracle.DatabaseError, exception:
            print ('Failed to prepare cursor')
            print Exception(exception)
            exit (1)
        try:
            trgtcrsr.executemany(None, recordsToWrite)        
        except cx_Oracle.DatabaseError, exception:
            print ('Failed to insert rows')
            print Exception(exception)
            exit (1)
        target_connection .commit()
        target_connection .close()            

I make the oracle connection like this

try:
    cnn = cx_Oracle.connect(user="####", password = "####", dsn = "####")
    trgtcrsr = cnn.cursor()
    print "Connected to Oracle"
except Exception as e:
    print e
    raise RuntimeError("Could not connect to Oracle")

The connection works fine. But when the line trgtcrsr.executemany(None, recordsToWrite) is executed it gives me a 'ORA-01036: illegal variable name/number' error

I have another script that uses the same method of writing a list of tuples to an oracle table with the trgtcrsr.prepare(SQL)/trgtcrsr.executemany(None, recordsToWrite) method and it works fine (granted its oracle to oracle) writing to oracle so I am not sure why I keep getting this error. I have tried changing data types and googling the error but cant find anything similar.

Any ideas?

Answer

rec[7] appears twice in the function BuildBindList().

I’m guessing this will cause the insert to fail as you passed it 16 columns to instantiate 15 bind variables in the insert statement.