Thursday, September 09, 2010

cx_Oracle and out cursor

If you need to call an Oracle stored procedure using cx_Oracle, and one of the arguments is an "out" cursor, this is how it is done.



connection = cx_Oracle.connect(connstr)
cursor = connection.cursor()
out = cursor.var(cx_Oracle.CURSOR)

items = cursor.callproc('EXAMPLE_PKG.get_some_data',
['02-SEP-2010',
'03-SEP-2010',
332123.0,
896798.0,
68567.0,
'xyz',
out
]

print items[6].fetchall()


A list of objects will be returned from callproc containing each of the arguments passed in. If any of the arguments passed are "out" arguments, they will have been modified to hold the results. In my case the seventh argument (items[6] --- zero indexed) returned a cursor. This cursor is then used to retrieve the result set.

I had trouble finding a good example of this usage pattern for cx_Oracle on the net, so here's one for the next lucky guy or girl who is digging for this answer.

5 comments:

Satin said...

Thanks for you. I just need such kind of example. :)

Gediminas said...

Just what i was looking for. Thanks

Chuck said...

OMFG! Where is this in the documentation Oracle? I've been trying for hours to get this to work. Thank you for posting!!!! How did you figure it out?

Devin Venable said...

It's been awhile since I figured it out, so I honestly can't recall. But it was enough of a pain that I thought to post it to help out the next unlucky bastard to end up in my shoes! Glad it helped.

Zhao Deng said...

great!!!

it's really useful to me.