Python-Oracle传递光标输出参数 [英] Python-Oracle Passing in a Cursor Out Parameter
本文介绍了Python-Oracle传递光标输出参数的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我试图在python和oracle db之间调用存储过程。
Oracle存储过程本质上是:
创建或替换过程sp_procedure(
cid int,
rep_date date,
ret out sys_refcursor
)是
begin
open ret for
select
...
end;
调用数据库的python代码是:
import cx_Oracle
从datetime导入日期
connstr='user/pass@127.0.0.1:2521 / XE'
conn = cx_Oracle.connect(connstr)
curs = conn.cursor()
cid = 1
rep_date = date(2011,06,30)
$ b b curs.callproc('sp_procedure',(cid,rep_date,curs))
curs.callproc('sp_procedure',(cid,rep_date,curs))
cx_Oracle.DatabaseError:ORA- 01036:非法变量名/数字
我还尝试传递一个字典作为关键字参数:
cid = 1
rep_date = date(2011,06,30)
call_params = { 'cid':cid,'rep_date':rep_date,'ret':curs}
curs.callproc('sp_procedure',(cid,rep_date,curs),call_params)
$ b感谢。
经过几个小时的搜索和跟踪/错误后,这里是解决方案:
解决方案cid = 1
rep_date = date(2011,06,30)
l_cur = curs.var(cx_Oracle.CURSOR)
l_query = curs.callproc ('sp_procedure',(cid,rep_date,l_cur))
l_results = l_query [2]
对于l_results中的行:
print row
#Column Specs
for row in l_results.description:
print row
I am trying to call a stored procedure between python and an oracle db. The problem I am having is passing a cursor out-parameter.
The Oracle stored procedure is essentially:
create or replace procedure sp_procedure( cid int, rep_date date, ret out sys_refcursor ) is begin open ret for select ... end;
The python code calling to the database is:
import cx_Oracle from datetime import date connstr='user/pass@127.0.0.1:2521/XE' conn = cx_Oracle.connect(connstr) curs = conn.cursor() cid = 1 rep_date = date(2011,06,30) curs.callproc('sp_procedure', (cid, rep_date, curs))
The error is:
curs.callproc('sp_procedure', (cid, rep_date, curs)) cx_Oracle.DatabaseError: ORA-01036: illegal variable name/number
I've also tried passing a dictionary as the keywordsParameters:
cid = 1 rep_date = date(2011,06,30) call_params = {'cid': cid, 'rep_date': rep_date, 'ret': curs} curs.callproc('sp_procedure', (cid, rep_date, curs), call_params)
Returns the same error.
Thanks.
解决方案After a couple of hours of googling and trail/error, here's the solution:
cid = 1 rep_date = date(2011,06,30) l_cur = curs.var(cx_Oracle.CURSOR) l_query = curs.callproc('sp_procedure', (cid,rep_date,l_cur)) l_results = l_query[2] for row in l_results: print row # Column Specs for row in l_results.description: print row
这篇关于Python-Oracle传递光标输出参数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文