RE: [cx-oracle-users] calling Oracle functions and getting result s back
Brought to you by:
atuining
Menu
▾
▴
RE: [cx-oracle-users] calling Oracle functions and getting result s back
|
From: McCafferty, C. <Chr...@Dr...> - 2003-08-01 10:06:35
|
Hi,
>>value = cursor.callfunc(funcName, parameters)
This seems simple and elegant, and mirrors calling a procedure. After all,
to the caller, a function is just a procedure that returns a value. I know
that there are more subtleties than that in Oracle, but that really is for
the function writer, not the API caller.
Let me know if you add this and I'll be more than happy to run some tests
against it.
Cheers,
Chris
-----Original Message-----
From: Anthony Tuininga [mailto:an...@co...]
Sent: 25 July 2003 18:31
To: McCafferty, Chris; cx_Oracle Mailing List
Subject: RE: [cx-oracle-users] calling Oracle functions and getting
result s back
You can certainly vote for it. But do you have any idea of the interface
that would make sense?
May I propose the following?
value = cursor.callfunc(funcName, parameters)
The value that would be returned would be the return value of the
function. If you needed out variables in addition, you could do the
following:
outVar = cursor.var(cx_Oracle.NUMBER)
value = cursor.callfunc(funcName, (1, outVar))
print "Value is:", value
print "OutVar is:", outVar.getvalue()
How does that sound? I realize this is different from callproc() but
since this isn't covered by the DB API it is not necessarily bad. It is
certainly better than the anonymous PL/SQL block method for the common
case, wouldn't you agree? Any comments?
On Fri, 2003-07-25 at 11:22, McCafferty, Chris wrote:
> Hi,
>
> I see - you do it through execute. Thanks.
> May i vote for a callFunc extension? It feels more 'pythonic'. But I see
> your point about having to set up the out var before hand - that kind of
> messes it up.
>
> Cheers,
>
> Chris
>
> -----Original Message-----
> From: Anthony Tuininga [mailto:an...@co...]
> Sent: 24 July 2003 14:42
> To: McCafferty, Chris
> Cc: 'cx-...@li...'
> Subject: Re: [cx-oracle-users] calling Oracle functions and getting
> results back
>
>
> You can do this with cx_Oracle but unfortunately the DB API does not
> cover the situation with functions. And Oracle provides less than
> stellar diagnostics when you attempt to call a function as a procedure.
> :-)
>
> The (simplest) way you can do this is as follows:
>
> import cx_Oracle
>
> connection = cx_Oracle.connect("user/pw@tns")
> cursor = connection.cursor()
> resultVar = cursor.var(cx_Oracle.NUMBER)
> cursor.execute("""
> begin
> :result := test_func(:param);
> end;""",
> param = 1,
> result = resultVar)
> print "Result:", resultVar.getvalue()
>
> I have considered extending the DB API to have a "callfunc" or something
> similar that would return the return value and the tuple of (possibly
> modified) values but that hasn't seemed all that useful to me. The only
> other possibility is to modify the tuple that is returned to include the
> return value at the beginning (or end) of the values. That doesn't
> appeal to me either.
>
> The DB API does not really handle the possibility of "out" values very
> well either and has no understanding of anonymous PL/SQL blocks. Thus
> the reason for the "var" call above.
>
> On Thu, 2003-07-24 at 03:32, McCafferty, Chris wrote:
> > Hi,
> >
> > Looking through the Aspn archives, there is discussion of returning
> results
> > from stored procedures, including Oracle procedures, but no clear-cut
> > example of calling a function. Is this possible using cx_oracle?
> >
> > For example, if I have this function, which simply increments the number
> > passed in:
> >
> > CREATE OR REPLACE FUNCTION TEST_FUNC(v_int in INTEGER)
> > RETURN NUMBER
> > AS
> > j INTEGER := NULL;
> > BEGIN
> > j := v_int + 1;
> > RETURN j;
> > END;
> > /
> >
> > If I try to invoke it with cx_oracle, it claims the _procedure_ doesn't
> > exist (where mTo below is a cursor created in the normal way):
> > >>> mTo.callproc('TEST_FUNC', [1])
> > Traceback (most recent call last):
> > File "<stdin>", line 1, in ?
> > cx_Oracle.DatabaseError: ORA-06550: line 1, column 7:
> > PLS-00221: 'TEST_FUNC' is not a procedure or is undefined
> > ORA-06550: line 1, column 7:
> > PL/SQL: Statement ignored
> >
> > Do I have to use cursor.execute() instead? In which case what format
> should
> > the sql string passed to execute take?
> >
> > Note that if I have a procedure that does the same thing (but doesn't
> > obviously return anything), then I can call it successfully.
> >
> > Can anyone help?
> >
> > Regards,
> >
> > Chris
> >
> >
> > ----------------------------------------------------------------------
> > If you have received this e-mail in error or wish to read our e-mail
> > disclaimer statement and monitoring policy, please refer to
> > http://www.drkw.com/disc/email/ or contact the sender.
> > ----------------------------------------------------------------------
> >
> >
> >
> > -------------------------------------------------------
> > This SF.Net email sponsored by: Free pre-built ASP.NET sites including
> > Data Reports, E-commerce, Portals, and Forums are available now.
> > Download today and enter to win an XBOX or Visual Studio .NET.
> >
>
http://aspnet.click-url.com/go/psa00100003ave/direct;at.aspnet_072303_01/01
> > _______________________________________________
> > cx-oracle-users mailing list
> > cx-...@li...
> > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
--
Anthony Tuininga
an...@co...
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
----------------------------------------------------------------------
If you have received this e-mail in error or wish to read our e-mail
disclaimer statement and monitoring policy, please refer to
http://www.drkw.com/disc/email/ or contact the sender.
----------------------------------------------------------------------
|
