CODEWITHSUNDEEP
pythonsqloracle-database
user5999614
user5999614

Reputation: 147

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?

Upvotes: 2

Views: 3522

Answers (2)

DEEPAK TOMAR
DEEPAK TOMAR

Reputation: 34

**#PYTHON SCRIPT TO COPY DATA FROM ORACLE TO SQL SERVER**
    import cx_Oracle 
    import pyodbc 
#Server Variables
    orServer = '10.xxx.x.xxx'
    orPort = 'xxxx'
    orService = 'MYSERV'
    orUser = 'ORMYUSER'
    orPassword = 'orpassword'
    sqlServer = 'SQLSERVER'
    sqlDatabase = 'MYDB'
#SQL Server Connection
    sqlConn = pyodbc.connect('Driver={SQL Server};'
                      'Server='+sqlServer+';'
                      'Database='+sqlDatabase+';'
                      'Trusted_Connection=yes;')
    sqlCursor = sqlConn.cursor()

#Oracle Connection
    dsn_tns = cx_Oracle.makedsn(orServer, orPort, service_name= orService ) 
    orConn = cx_Oracle.connect(user= orUser, password= orPassword, dsn=dsn_tns) 
    orCursor = orConn.cursor()

#Get data from Oracle Server
    orCursor.execute("""SELECT  ID
                                ,NAME
                                ,SEX
                                ,ADDRESS
                                PHONE
                        FROM DetailsTable"""
                    )

    orColumns =['ID',
                'NAME',
                'SEX',
                'ADDRESS',
                'PHONE']

#Creating Strings for insert statement to load data into SQL Server
    cValues = str(orColumns).replace("[","(").replace("]",")").replace("'","")

    x = len(orColumns) 

    i = 0

    ab = []

    while i < x:
        i = i+ 1
        ab.append("?")

    values = str(ab).replace("[","(").replace("]",")").replace("'","")

#Load data to SQL server
    sqlCursor.executemany("INSERT INTO [MYDB].[dbo].[DetailsTable]"+ cValues+ " VALUES "+ values ,orCursor)
    sqlConn.commit()
    sqlConn.close()
    orConn.close()

Upvotes: 1

Peter M.
Peter M.

Reputation: 713

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.

Upvotes: 1

Related Questions