0

i am trying to insert data in database 

con = cx_Oracle.connect('---------')
cur = con.cursor()

var1=3
var2=4

cur.execute('''
                insert into some_table (E,ECA_ID,E3,E4,E5,ARD_,ARD_N,ARD_NA,CARD_N,NAME,RANKNUM) 
                VALUES
                (1,%d,%s,4,5,'1','w','d','g', 'f',1)
                ''',(var1,var2))
con.commit()

and it gives me error

DatabaseError: ORA-01036: illegal variable name/number

so when i tried it with code 

con = cx_Oracle.connect('---------')
cur = con.cursor()

cur.execute('''
                insert into some_table (E,ECA_ID,E3,E4,E5,ARD_,ARD_N,ARD_NA,CARD_N,NAME,RANKNUM) 
                VALUES
                (1,1,3,4,5,'1','w','d','a', 'b',1)
                ''')
con.commit()

it works, so what is problem?

i am working in jupyter notebook, win 10

Bob Jarvis - Слава Україні
  • 48,992
  • 9
  • 77
  • 110
Vaso Miruashvili
  • 101
  • 1
  • 11
  • what about changing `%d` to `%s` ... – Bruno Vermeulen Oct 28 '19 at 11:22
  • no, it says same as before – Vaso Miruashvili Oct 28 '19 at 11:24
  • 1
    Are you sure `%d` and `%s` are allowed? Different implementations of the Python DB-API are allowed to use different placeholders. If I look at [`cx_Oracle.paramstyle`](https://www.python.org/dev/peps/pep-0249/#paramstyle) it says "named", so you have to follow the Oracle rules here: https://cx-oracle.readthedocs.io/en/latest/user_guide/sql_execution.html#sqlexecution – Matthias Oct 28 '19 at 11:55

1 Answers1

1

I never use oracle, but I think it's possible that you should scape %d,%s to '%d','%s'.

When you use '%' in a string (in java, for example) means a pattern to format the output.

Or maybe here DatabaseError: ORA-01036: illegal variable name/number there's some solution to a problem very similar.

Regards, Juliano.