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.