>>> 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 = 1In 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.
>>> curs.callproc('times_two',[2, n])
[2, 4]
>>> n
1
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)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.
> curs.callproc('times_two',[5,n])
[5, 10.0]
>>> n
<cx_Oracle.NUMBER object at 0xb7cf2480>
>>> n.getvalue()
10.0
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:
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.
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
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.
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.
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.
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.
thanks for the snippets it saved me
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"
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.
Nice post. I search oracle and python related topics, and keep coming back to your blog!
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,
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...
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.
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
Thanks Catherine. Your posts have helped me out once again.
Post a Comment