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