Python-Oracle passing in Cursor Out parameter

Question

I am trying to call a stored procedure between python and oracle db. The problem I am facing is passing the out out parameter.

Oracle stored procedure essentially:

create or replace procedure sp_procedure( cid int, rep_date date, ret out sys_refcursor ) is begin open ret for select ... end; 

Calling Python code in a database:

 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)) 

Error:

 curs.callproc('sp_procedure', (cid, rep_date, curs)) cx_Oracle.DatabaseError: ORA-01036: illegal variable name/number 

I also tried using the dictionary as keywords. Parameters:

 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) 

It returns the same error.

Thanks.

Answer 1

After several hours of working with the search engine and following / error, here is 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 

Answer 2

Try the following:

 curs = con.cursor() out_curs = con.cursor() curs.execute(""" BEGIN sp_procedure(:cid, :rep_date, :cur); END;""", cid=cid, rep_date=rep_date, ret=outcurs)