Python-Oracle Passing in a Cursor Out Parameter

Question

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/[email protected]: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.

Check Oracle's example ref_cursor.py for how to do this, and how to tune the fetch. — Christopher Jones
– Christopher Jones, Commented May 3 at 2:40

Jama · Accepted Answer · 2011-07-26 11:48:19Z

13

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

answered Jul 26, 2011 at 11:48

Jama

2954 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

HugoTai Over a year ago

remember all cap CURSOR

Scott A · Accepted Answer · 2011-07-25 21:24:22Z

1

Try this:

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)

answered Jul 25, 2011 at 21:24

Scott A

7,8643 gold badges35 silver badges47 bronze badges

1 Comment

Jama Over a year ago

Hi Scott, I managed to get the callproc() method to work. Thanks.

Collectives™ on Stack Overflow

Python-Oracle Passing in a Cursor Out Parameter

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related