[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