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.

Advertisement

Answer