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
– Barmar, Commented Mar 28, 2022 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: stackoverflow.com/a/33882805/230471 — William Robertson
– William Robertson, Commented Mar 28, 2022 at 18:45

Christopher Jones · Accepted Answer · 2022-03-28 22:45:22Z

0

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.

answered Mar 28, 2022 at 22:45

Christopher Jones

11.1k7 gold badges32 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to bind oracle sql procedure variable in python

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related