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

Anthony Tuininga anthony at computronix.com
Wed Dec 10 10:56:41 EST 2003


On Wed, 2003-12-10 at 08:47, Harri Pasanen wrote:
> Thanks for the quick reply, seems to work fine.

You're welcome.

> Now that I have your attention, next question ;-)

Of course. :-)

> 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?

You can't do so by calling cursor.callfunc() or cursor.callproc() since
these by definition require a list (see the DB API spec). That said,
there is no reason that cx_Oracle could not do so as an extension to the
DB API. I'll keep that in mind for some future release. Just as a side
note, most times default parameters are used, they are placed at the end
so that they can safely be ignored in most cases. Putting a required
parameter at the end requires named parameter passing -- but that's a
matter of style that is up to you, not me. :-)

In the meantime, however, you can quite easily "roll your own" so to
speak with an anonymous PL/SQL block. Specifically, something like this:

retValVar = cursor.var(cx_Oracle.NUMBER)
cursor.execute("""
        begin
          :retval := foo(arg5 => :arg5, arg6 => :arg6, cr1 => :cur);
        end;""",
        arg5 = 1,
        arg6 = "Another parameter",
        cr1 = cursorToBind,
        retval = retValVar)
print "Result is:", retValVar.getvalue()
...

Hope that helps.

> 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
-- 
Anthony Tuininga
anthony at computronix.com
 
Computronix
Distinctive Software. Real People.
Suite 200, 10216 - 124 Street NW
Edmonton, AB, Canada  T5N 4A3
Phone:	(780) 454-3700
Fax:	(780) 454-3838
http://www.computronix.com




More information about the DB-SIG mailing list