Friday, April 28, 2006

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.

16 comments:

Paul Moore said...
This comment has been removed by a blog administrator.
Paul Moore said...

It's possibly more readable to use the fact that callproc returns the arguments:

n, result = c.callproc('times_two', [3, 0])

It's annoying that you need to include a placeholder to get the value of your "in" parameter (n above) - but you can use a dummy variable like "_" if you want.

It's also annoying that you have to provide an initial value for the "out" variable, but that's necessary as cx_Oracle uses that to guess the type you want for the variable. Look at the following:

>>> n, result = c.callproc('times_two', [3, 0])
>>> result
6
>>> n, result = c.callproc('times_two', [3, None])
>>> result
'6'
>>> n, result = c.callproc('times_two', [3, 'xxx'])
>>> result
'6'

So, the value passed in for the out parameter is ignored, but its type affects the result.

Anonymous said...

Catherine,
Just found your page today. I'm new to python but not pl/sql. Now I can join the two together to do some powerful stuff. Saw your OTN article - very helpful, as well as the stuff on this page. Thanks much!

Ken @ NOAA

z0mg said...

Hi, i'm still a newbie to oracle & python and have a small question for you. Let's say i have a procedure wich produdes more than one result. something like a "select * from..." where you get multiple rows back. i need to get those multiple rows in my python programm but i don't know how.

z0mg said...

Hi, i'm still a newbie to oracle & python and have a small question for you. Let's say i have a procedure wich produdes more than one result. something like a "select * from..." where you get multiple rows back. i need to get those multiple rows in my python programm but i don't know how.

z0mg said...

Hi, i'm still a newbie to oracle & python and have a small question for you. Let's say i have a procedure wich produdes more than one result. something like a "select * from..." where you get multiple rows back. i need to get those multiple rows in my python programm but i don't know how.

Catherine said...

No problem, z0mg... after you've called execute(), then cursor.fetchall() will get you the entire result set as a list. Alternately, you can use the cursor object itself as an iterator, looping through with 'for row in cursor'. See http://www.oracle.com/technology/pub/articles/devlin-python-oracle.html for more details.

Felipe said...

thanks for the snippets it saved me

Vladimir said...

Why didn't you post any example with callfunc? It looks like it isn't working as advertised... At least in my case, it keeps returning the following: "ProgrammingError: number of elements must be an integer"

Rodrigo said...

Hi Catherine. Thanks for your blog!

I'm trying to execute a callproc that returns an array at Oracle.

I'm trying to define an output variable the same way you explained but using arrayvar but without success. How can I do it?

Thanks and Regards.

longzhou said...

Nice post. I search oracle and python related topics, and keep coming back to your blog!

satya said...

Hi Catherine,
I am a newbie developer, and am stuck at some place where i guess u cud help. i am trying to do some stuff you already did i guess with oracle-jython (read in one of your posts on oracle forum). Im trying to create an external stored procedure from jython by converting jython code to java using the jythonc utility. I do get the code in java but it seems jythonc cannot create static methods in java from jython code; and External procedures require static methods.
i could use a java wrapper class as a work around but it doesn seem an elegant solution. Did you figure out how to do it? Please let me know if you did. I will really appreciate your help.

thanks,

Catherine said...

Hi, Satya!

I'm sorry to say, I think you mistook my *daydreaming* about Jython stored procedures in Oracle for actually *accomplishing* it. I haven't! The only reference material I've found on it comes from back when jythonc was still available.

If I get it figured out, I promise to post on my blog! For now, though, I think you're closer to success than me...

Alex said...

Thanks a lot for a really useful and clearly written article. This is exactly what I needed to get a stored procedure working in my Python program.

Vlad Sanchez said...

I'm trying to accomplish something similar with an Object Type.

I instantiate the Object Type in a cursor:

SELECT TFoo.createObject('name','value') FROM DUAL;

All I get back is a 'dumb' cx_Oracle.OBJECT with no reference to its member data. I tried to declare a cx_Oracle.OBJECT variable to access it's data but it errored out (NotSupportedError).

Any ideas on how to handle this case? I'm tyring to map these pL/SQL object types to my module namespace.

Will appreciate your feedback.

-Vlad

Devin Venable said...

Thanks Catherine. Your posts have helped me out once again.