CODEWITHSUNDEEP
pythonoracle-databasems-access
Req_7
Req_7

Reputation: 85

"ORA-01036 illegal variable name/number" at INSERT INTO query

I'm creating a Python code that fetches data from an MS Access table and inserts it into an Oracle SQL table, but when I run the code, I get the error ORA-01036 illegal variable name/number. This error is occurring in the INSERT INTO statement. I don't know what it could be.

import pyodbc
import cx_Oracle

# Set up the Microsoft Access connection
access_conn_str = (
    r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};'
    r'DBQ=C:\MyFolder\ACC_BASE.MDB;'
    )
access_conn = pyodbc.connect(access_conn_str)

# Define the Oracle SQL connection string
oracle_conn_str = cx_Oracle.makedsn("MyConnection", "MyPort", "MySID")

# Create a connection to the Oracle SQL database
oracle_conn = cx_Oracle.connect(user="MyUser", password="MyPassword", dsn=oracle_conn_str)

# Create a cursor for each connection
access_cursor = access_conn.cursor()
oracle_cursor = oracle_conn.cursor()

# Execute the select statement to extract data from the Access table
access_cursor.execute('SELECT * FROM ACC_TABLE')

# Loop through the rows of the Access table and insert them into the Oracle SQL table
for row in access_cursor.fetchall():
    oracle_cursor.execute(
        'INSERT INTO ORACLE_TABLE (COD, LEV, AZET, HUES) VALUES (?, ?, ?, ?)',
        [row[0], row[1], row[2], row[3]]
    )

# Commit the changes to the Oracle SQL table
oracle_conn.commit()

# Close the cursors and connections
access_cursor.close()
access_conn.close()
oracle_cursor.close()
oracle_conn.close()

Upvotes: 1

Views: 3132

Answers (2)

Christopher Jones
Christopher Jones

Reputation: 10611

This is untested code, but shows the kind of thing you should be doing:

import pyodbc
import cx_Oracle as oracledb  # really should upgrade from cx_Oracle, but since you have cx_Oracle I used it

access_conn_str = (
    r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};'
    r'DBQ=C:\MyFolder\ACC_BASE.MDB;'
    )
access_conn = pyodbc.connect(access_conn_str)

# SIDs are obsolete but since I don't know your service name, and you had this code I didn't change it
oracle_conn_str = oracledb.makedsn("MyConnection", "MyPort", "MySID")

oracle_conn = oracledb.connect(user="MyUser", password="MyPassword", dsn=oracle_conn_str)

access_cursor = access_conn.cursor()
oracle_cursor = oracle_conn.cursor()

access_cursor.execute('SELECT * FROM ACC_TABLE')
while True:
    rows = access_cursor.fetchmany(1000)
    if not rows:
        break
    oracle_cursor.executemany('INSERT INTO ORACLE_TABLE (COD, LEV, AZET, HUES) VALUES (:1, :2, :3, :4)', rows)
oracle_conn.commit()

I left the clean up of resources to the driver to handle, so the order is done correctly.

Upvotes: 0

Saxtheowl
Saxtheowl

Reputation: 4658

The message

ORA-01036 illegal variable name/number

indicate that there might be a problem with the variable you are trying to put into the database, try checking the names of columns in the ORACLE_TABLE and check if they match what there is in your INSERT command.

Upvotes: 0

Related Questions