user5999614 user5999614 - 5 months ago 34
SQL Question

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.