oracle / python-oracledb

Python driver for Oracle Database conforming to the Python DB API 2.0 specification. This is the renamed, new major release of cx_Oracle
https://oracle.github.io/python-oracledb
Other
308 stars 61 forks source link

ORA-01036: illegal variable name/number oracledb (python) #269

Closed danilyef closed 6 months ago

danilyef commented 6 months ago

I want to insert data into my Oracle table, which has the following structure:

SUBJECT             VARCHAR2(4000 BYTE)
TEXT                LONG
PREDICTED_CATEGORY  VARCHAR2(1000 BYTE)
TRUE_CATEGORY       VARCHAR2(1000 BYTE)
PROBABILITY         VARCHAR2(4000 BYTE)
NUMBER_ANONYMIZED_TOKENS    VARCHAR2(1000 BYTE)
CASEID              VARCHAR2(1000 BYTE)

For this I have prepared row according to the documentation (https://cx-oracle.readthedocs.io/en/latest/user_guide/bind.html#binding-by-name-or-position)

data = {"subject": 'anon_subj', "txt": 'anon_text', "pred_cat": pred_class,"true_cat": "", "prob": str(probability),"num_anon": num_anon_tokens ,"caseid": str(caseID)}

I have prepared SQL Insert statement as well:

statement= "INSERT INTO MY_ORACLE_TABLE(SUBJECT, TEXT, PREDICTED_CATEGORY, TRUE_CATEGORY, PROBABILITY, NUMBER_ANONYMIZED_TOKENS, CASEID) VALUES (:subject, :txt, :pred_cat, :true_cat, :prob, :num_anon, :caseid)"

I have a executed the following code:

oracledb.init_oracle_client()
con = oracledb.connect(DB_CONNECTION_STRING, encoding='UTF-8', nencoding='UTF-8')
cur = con.cursor()
cur.bindarraysize = 1       
cur.execute(statement, data)
con.commit()
cur.close()

After Execution I get the error ORA-01036: illegal variable name/number oracledb (python), which is most definitely connected to my binding variables. I have looked up many posts on stackoverflow with the same question, but couldn't figure it out.

cjbj commented 6 months ago

This was also posted at https://stackoverflow.com/q/77687815/4799035 and resolved as a user error.