How to bind oracle sql procedure variable in python

Question

I have the below oracle sql procedure which is called by python using an sql file. When I run the below procedure with actual value for account number, it gives as pop-up -> enter binds where I get to enter customer name value.

Declare  
   Accountnum   varchar2(200); 
   Startdtm     date;  
   Customername varchar2(200);
Begin  
   Accountnum := null; 
   Startdtm := null;
   Procedurename( Accountnum => ‘$$ACCNUM’, —- value is ‘19283-1’ Startdtm => Startdtm,  Customername => Customername);
  :Customername := Customername; —-value is ‘19283’
End;  
/ 
Commit;

This sql procedure file is called in python as below:

def executeSQL(self, accnum, custname, sqlfile):
    f = open(sqlfile)
    fullSql = f.read()
    replacedSQL = fullSql.replace(“$$ACCNUM”, str(accnum))
    self.cur.callproc(“dbms_output.enable”)
    var1 = self.cur.var(str)
    self.cur.execute(replacedSQL, var1 = custname)
    self.cur.execute(“commit”)

After running this in python, the account num is getting replaced but for customer name it is printing the below error:

ORA-01036: illegal variable name/number.

Could someone please explain where I went wrong.

You have curly quotes in the Python script. Please recopy with correct quotes. And put it in just a code block, not a quote block. — Barmar, Mar 28 '22 at 15:25
Is this binding a variable or concatenating a literal value? I don't know Python, but shouldn't it be something like this: https://stackoverflow.com/a/33882805/230471 — William Robertson, Mar 28 '22 at 18:45

score 0 · Answer 1 · answered Mar 28 '22 at 22:45

Change var1 to the name of the bind variable in the SQL text, i.e. to Customername:

self.cur.execute(replacedSQL, Customername = custname)

Overall I would prefer using a dictionary as shown in the comment link on your question.

Ideally you should also bind the account name instead of calling replace. Otherwise you are still open to SQL injection attacks. And you also don't get the scalability benefits of statement reuse.

An unrelated tweak would be to call:

connection.commit()

instead of going through the overhead of sending a SQL COMMIT statement.

Or better would be to use autocommit.

How to bind oracle sql procedure variable in python

1 Answers1