[DB-SIG] How to call function with cursor in-out parameter type?

[Harri Pasanen]

Thanks for the quick reply, seems to work fine.
Now that I have your attention, next question ;-)

I have a bunch of Oracle functions with signature

FUNCTION foo RETURNS NUMBER 
arg1 type1 in default 			(typically = null)
arg2 type2 in default 			(typically = null)
.
.
.
argN typeN in default			(typically = null)
cr1 ref cursor in/out

So most arguments can be omitted.

The documentation does not mention dictionary style arguments in the 
context of callproc/callfunction?  Do I have other options than to 
dig out the metadata for the function and build the parameter list 
from that, if I want to write a generic python function that is 
capable of calling these functions, without the need to fill in the 
default arguments?

Thanks,

Harri

On Wednesday 10 December 2003 15:50, Anthony Tuininga wrote:
> Its actually easier than what you are trying to do... :-)
>
> cursorToBind = connection.cursor()
> result = cursor.callfunc("myfunc", cx_Oracle.NUMBER, (1,
> cursorToBind)) print "The result is:", result
> print "The result set is:"
> for row in cursorToBind:
>     print "Row:", row
>
> In other words, create a cursor and then simply bind it where you
> want it. If you have difficulty making this work, let me know.
>
> On Wed, 2003-12-10 at 05:03, Harri Pasanen wrote:
> > Hello,
> >
> > I'm a bit at loss how to make the following type of a call from
> > Python DB API, cx_Oracle in this case:
> >
> > SQL> desc myfunc
> > FUNCTION myfunc RETURNS NUMBER
> >  Argument Name                  Type                    In/Out
> > Default?
> >  ------------------------------ ----------------------- ------
> > --------
> >  PID                            VARCHAR2                IN    
> > DEFAULT CR1                            REF CURSOR             
> > IN/OUT
> >
> > SQL> var crs refcursor
> > SQL> var ret number
> > SQL> exec :ret := myfunc('YVESX',:crs)
> >
> > SQL> print crs
> >
> >         ID LIMIT_ID
> > ...
> >
> > So on return crs is a cursor containing a result set.
> >
> > My initial idea was to do
> >
> > v = cursor.var(cx_Oracle.CURSOR)
> > res = cursor.callfunc("myfunc", cx_Oracle.NUMBER, v)
> >
> > but that does not work, I'm getting
> >
> > "cx_Oracle.NotSupportedError: Variable_TypeByPythonType():
> > unhandled data type"
> >
> > Any hints on how to go about this?
> >
> > Harri
> >
> >
> >
> >
> >
> > _______________________________________________
> > DB-SIG maillist  -  DB-SIG at python.org
> > http://mail.python.org/mailman/listinfo/db-sig