Stored procedures from cx_Oracle

A couple of people have asked me about calling Oracle stored procedures from cx_Oracle. It's taken me a while to answer, because... I didn't know! I'd only had experience doing them the 'dumb' way:

>>> ora = cx_Oracle.Connection('scott/tiger@orcl')
>>> curs = ora.cursor()
>>> curs.execute('execute immediate myStoredProc(:a)',{'a':'the letter a'})

... but, of course, that won't do if (for instance) you want OUT variables. So I did a little research. cx_Oracle provides callproc and callfunc, but using them can get squirrely. Say you have PROCEDURE times_two(n IN NUMBER, result OUT NUMBER).

>>> n = 1
>>> curs.callproc('times_two',[2, n])
[2, 4]
>>> n
1

In other words, if you just pass a regular Python variable to callproc, the value won't actually change, OUT mode notwithstanding. If you want the new value, you'll just have to assign it there from callproc's return value.

Alternately, you can prepare the way by setting up your in/out variable as an instance of a special cx_Oracle object type, as follows...

>>> n = curs.var(cx_Oracle.NUMBER)
> curs.callproc('times_two',[5,n])
[5, 10.0]
>>> n
<cx_Oracle.NUMBER object at 0xb7cf2480>
>>> n.getvalue()
10.0

Pre-setting a variable's type? Calling .getvalue() just to see the contents? What an un-Pythonic pain! As far as I know, for the time being, cx_Oracle and PL/SQL procedures with IN-OUT parameters are simply two great tastes that do not taste great together. You can do it, you just won't feel like you're having Pythonic fun.

On the plus side, if the stored function or procedure is within a PL/SQL package, callproc accepts that in the way you'd guess:
> curs.callproc('multiplication_package.times_two',[5,n])
[5, 10.0]

Oh, and it looks like sqlWrap.py wasn't handling .callproc. I've posted a correction.