CODEWITHSUNDEEP
oracle-databasepython-2.7csvsql-insertcx-oracle
Ritesh Sil
Ritesh Sil

Reputation: 41

cx_Oracle.DatabaseError: ORA-01036: illegal variable name/number

I am trying load a csv file into the oracle database and facing this error :

cx_Oracle.DatabaseError: ORA-01036: illegal variable name/number

Can you please help to understand the root cause and possible fix for this issue?

def main():
    ConStr = 'UserName/PWD@END_POINT'
    con = cx_Oracle.connect(ConStr)
    cur = con.cursor()
    with open('Persons.csv','r') as file:
                    read_csv = csv.reader(file,delimiter= '|')
                    sql = "insert into Persons (PERSONID,LASTNAME,FIRSTNAME,ADDRESS,CITY) values (:1,:2,:3,:4,:5)"
                    for lines in read_csv :
                            print(lines)
                            cur.executemany(sql,lines)
    cur.close()
    con.commit()
    con.close();

My csv file looks like below :

PERSONID|LASTNAME|FIRSTNAME|ADDRESS|CITY
001|abc|def|ghi|jkl
002|opq|rst|uvw|xyz

Upvotes: 4

Views: 6940

Answers (1)

Christopher Jones
Christopher Jones

Reputation: 10506

From the Oracle documentation:

import cx_Oracle
import csv

. . .

# Predefine the memory areas to match the table definition
cursor.setinputsizes(None, 25)

# Adjust the batch size to meet your memory and performance requirements
batch_size = 10000

with open('testsp.csv', 'r') as csv_file:
    csv_reader = csv.reader(csv_file, delimiter=',')
    sql = "insert into test (id,name) values (:1, :2)"
    data = []
    for line in csv_reader:
        data.append((line[0], line[1]))
        if len(data) % batch_size == 0:
            cursor.executemany(sql, data)
            data = []
    if data:
        cursor.executemany(sql, data)
    con.commit()

Upvotes: 1

Related Questions