python连接oracle数据库返回游标_Python-Oracle传递光标输出参数

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