如何在python中绑定oracle sql过程变量
我有下面的 oracle sql 过程，它由 python 使用 sql 文件调用。 当我使用帐号的实际值运行以下过程时，它会弹出 ->输入绑定，我可以在其中输入客户名称值。
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;
这个 sql 过程文件在 python 中调用如下：
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”)
在 python 中运行此文件后，帐户编号被替换，但对于客户名称，它打印以下错误：
ORA-01036: 非法变量名称/编号。
有人可以解释一下我错在哪里吗？
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
将
var1更改为SQL文本中的绑定变量的名称，即coduname：总的来说，我更喜欢使用您的问题的注释链接中的字典。
理想情况下，您还应该绑定帐户名而不是调用
替换。否则，您仍然接受SQL注入攻击。而且您也不会获得陈述重用的可伸缩性好处。无关的调整将是致电：
而不是通过发送SQL Commit语句的开销。
或者更好的是使用自动加入。
Change
var1to the name of the bind variable in the SQL text, i.e. toCustomername: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:
instead of going through the overhead of sending a SQL COMMIT statement.
Or better would be to use autocommit.